Forum Discussion
How to create measures across multiple fact tables with multiple categories (fields/columns)?
- 3 years ago
I think you could create a single dimension table like
DIM Table = DISTINCT ( UNION ( SUMMARIZE ( 'Funds', 'Funds'[Org], 'Funds'[Center], 'Funds'[Type_Code], 'Funds'[Location] ), SUMMARIZE ( 'Processed', 'Processed'[Org], 'Processed'[Center], 'Processed'[Type_Code], 'Processed'[Location] ) ) )This doesn't need to be linked to the fact tables. You could then create a measure like
Funds Minus Processed = VAR TotalFunds = CALCULATE ( SUM ( 'Funds'[Funds] ), TREATAS ( VALUES ( 'Dim Table'[Org] ), 'Funds'[Org] ), TREATAS ( VALUES ( 'Dim Table'[Center] ), 'Funds'[Center] ), TREATAS ( VALUES ( 'Dim Table'[Type_Code] ), 'Funds'[Type_Code] ), TREATAS ( VALUES ( 'Dim Table'[Location] ), 'Funds'[Location] ) ) VAR TotalProcessed = CALCULATE ( SUM ( 'Processed'[Processed] ), TREATAS ( VALUES ( 'Dim Table'[Org] ), 'Processed'[Org] ), TREATAS ( VALUES ( 'Dim Table'[Center] ), 'Processed'[Center] ), TREATAS ( VALUES ( 'Dim Table'[Type_Code] ), 'Processed'[Type_Code] ), TREATAS ( VALUES ( 'Dim Table'[Location] ), 'Processed'[Location] ) ) RETURN TotalFunds - TotalProcessedand use that in visuals with columns from the dimension table
- 3 years ago
Its maybe a 7 complexity-wise. The problem is that you can't create a normal dimension table with one-to-many relationship to the fact tables because there isn't a single column to use.
What the measure is doing is taking all the values for the dimension columns which are visible in the current filter context, and then telling the model to use those values as filters on the fact table. What this means in practice is that when calculating the measure for the chart it will calculate it individually for each combination of center, org and type, and so it will sum the values for only those rows of the fact table with the same center and same org and same type. Because of the way the dimension table is set up it will work for any combination of columns from the dimension table.
Hope this makes it a bit clearer.
Have a look at https://maxpowerbi.pro/facts-facts-facts-five-ways-of-combining-several-fact-tables-in-a-powerbi-report/#more-177 for a good discussion