DAX measure with left outer join, multiple to multiple

sodec — Thu, 25 May 2023 12:58:28 GMT

I have one table in data model with one row per employee, their current end previous manager, and their current and previous output:

EmployeeID	Current Manager	Current Output	Previous Manager	Previous Output
1	A	10	B	5
2	A	20	A	15
3	B	30	A	40
4	B	5	B	10

In the reports view, I show the sum of current and previous output by current and previous manager respectively:

Current Manager	Current Output
A	30
B	35

And:

Previous Manager	Previous Output
A	55
B	15

I would like to left join the 'current output' table with the 'previous output' table on Current Manager = Previous Manager so I can calculate the output delta. What I want is this:

Current Manager	Current Output	Previous Output	Delta Output
A	30	55	-25
B	35	15	20

I have tried writing a measure to give the answer, but can't figure out how to pass the values of the delta:

Create Delta Table =

VAR x2=GENERATEALL(

SUMMARIZE('EmployeeTable', EmployeeTable'[Current Manager],

"Sum Current Output", CALCULATE (SUM('EmployeeTable'[Current Output])))

,SUMMARIZE('EmployeeTable', 'EmployeeTable'[het_name],

"Sum Previous Output", CALCULATE (SUM('EmployeeTable'[Previous Output])))

)

RETURN

Re: DAX measure with left outer join, multiple to multiple

johnt75 — Thu, 25 May 2023 13:21:05 GMT

You can create measures like

Current Output Measure = SUM( 'Table'[Current Output]) Previous Output Measure = CALCULATE( SUM( 'Table'[Previous Output]), REMOVEFILTERS( 'Table'[Current Manager]), TREATAS( VALUES( 'Table'[Current Manager]), 'Table'[Previous Manager]) ) Delta = [Current Output Measure] - [Previous Output Measure]

Re: DAX measure with left outer join, multiple to multiple

sodec — Thu, 25 May 2023 13:27:05 GMT

This works well and is so much simpler than what I was trying to do. Thank you.

topic Re: DAX measure with left outer join, multiple to multiple in DAX Commands and Tips

DAX measure with left outer join, multiple to multiple

Re: DAX measure with left outer join, multiple to multiple

Re: DAX measure with left outer join, multiple to multiple