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

[PBInonpro]

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
Z	Z1	GG	SOUTH	300
W	W1	FF	NORTH	600
W	W2	FF	SOUTH	100
Z	Z1	FF	NORTH	500
Z	Z2	GG	SOUTH	200

Table 2 (Processed)

Org	Center	Type_Code	Location	Processed
Z	Z1	FF	SOUTH	5
Z	Z1	GG	SOUTH	7
W	W1	FF	NORTH	8
Z	Z1	GG	SOUTH	10
Z	Z2	GG	SOUTH	15
W	W1	FF	NORTH	20
W	W1	FF	NORTH	1
Z	Z1	FF	NORTH	2
Z	Z1	GG	SOUTH	9

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. 

[johnt75 · Accepted solution]

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 · Accepted solution]

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]

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 · Accepted solution]

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.

[PBInonpro]

It does! but I still need to practice. Is there any program of method to see the step by step of a DAX formula playing out? like a compiler in Python or C++?

I guess my brain still isn't understanding how a DAX formula can input/output multiple aspects (multiple columns of reference). 

[johnt75]

The best advice I can give is to check out https://www.sqlbi.com/articles/ , read their articles, watch their videos and if you can take their Mastering DAX training course.