generateall
1 Topic
DAX measure with left outer join, multiple to multiple
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 x2Solved856Views0likes2Comments