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):

 

OrgCenterType_CodeLocationFunds
ZZ1FFSOUTH500
ZZ1GGSOUTH300
WW1FFNORTH600
WW2FFSOUTH100
ZZ1FFNORTH500
ZZ2GGSOUTH200

 

Table 2 (Processed)

OrgCenterType_CodeLocationProcessed
ZZ1FFSOUTH5
ZZ1GGSOUTH7
WW1FFNORTH8
ZZ1GGSOUTH10
ZZ2GGSOUTH15
WW1FFNORTH20
WW1FFNORTH1
ZZ1FFNORTH2
ZZ1GGSOUTH9


I want to ensure what I am doing is correct. 

I want to know the difference between 'Funds' and 'Processed' per category.

I would create a new table for the distinct values between the tables (Org, Center, Type_Code and Location). 

I would join each DimTable to each Fact Table. 

 

Now at this part, I have all active relationships but the W-W2-FF-South-500 lines from 'Funds' doesn't populate as there is no 'processed' hit against this line. So I filter both ways, and then I get errors. At this point, I should make all of the relationships inactive and use seperate 'USERELATIONSHIP' measures for each category? it is the only way I can get it to work, but it seems very reduntant. 

 

The first end product will be a summary table/chart for each category. 'Funds' - 'Processed' per unique Center. Per Unique Type_Code, etc. 

 

Any help would be appreciated. 

Need Help
Tutorial Requests
  • johnt75's avatar
    johnt75
    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.

6 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    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

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

      Thanks!

      How complicated is this on a scale of 1-10? I am looking at it but not fully understanding it. 


      I like that it has this, but how is a single measure able to iterate over everything? 

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        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.