Forum Discussion

PBInonpro's avatar
PBInonpro
Icon for Helper III rankHelper III
3 years ago
Solved

How to create measures across multiple fact tables with multiple categories (fields/columns)?

If you have two Fact Tables and  you want to create measures between the two; what are the best practices? Table 1 (Funds):   Org Center Type_Code Location Funds Z Z1 FF SOUTH 500 ...
  • johnt75's avatar
    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 - TotalProcessed
    

    and use that in visuals with columns from the dimension table

  • johnt75's avatar
    johnt75
    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.