SSRS dataset with DAX and multiple fact tables

Question

I want to create an SSRS report connecting to a SSAS tabular database. This SSRS report should contain a matrix with costs and budgeted costs grouped by years and cost types.

Matrix

Now my problem is, that the cost table and the budgeted cost table are both fact tables. I do not know enough DAX to get columns from both fact tables. Right now I can only create a dataset with the costs or the budgeted costs. I thought about two datasets in two matrixes. But that’s obviously not a good solution.

Data model

This is a samplee of my (not working) DAX code.

EVALUATE
 (
    FILTER (
        SUMMARIZE (
            Costs,
            Costs[IScost],
            Time[year],
            CostType[name],
            PlanedCosts[ISplanedcost]
        ),
        Time[year] = 2018
    )
)

I don’t think that this is a hard task, but so far, I did not find a solution with DAX and SSRS. I can't belief that this is not possible with DAX. Or do I really need to use, for example, an MDX query? I would appreciate if someone could guide me in the right direction.

you also know you can build the DAX within SSRS right. Make sure to save it though or else you will have to click and drag each element all over again. — junketsu
@junketsu Thank you for your input. But it's not a filter problem. The query does not work with or without filter. It only works when I either remove "Costs[IScost]" or "PlanedCosts[ISPlanedcost]". Isn't the automatically built query in MDX? I will try anyway of course. — J. N.

MoazRub MoazRub · Accepted Answer · 2018-12-02T20:33:51

So here is an example based on Adventure Works. It has MDX query for exactly what you want to achive. Just replace the Measures and Dimensions and you will be good

Select 
non empty //Non empty eliminates the null combinations
(
[Date].[Calendar Year].[Calendar Year], // Place your Date dimension here 
{[Measures].[Internet Sales Amount],[Measures].[Internet Order Quantity]} //These will be the two measures cost & budget. 
)
on columns,
non empty //Non empty eliminates the null combinations
[Product].[Subcategory].[Subcategory] //Place your cost type dimension here 
on rows 
from [Adventure Works]

SSRS dataset with DAX and multiple fact tables

1 Answers