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.

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.

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.

Answer 1

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]