Forum Discussion

sodec's avatar
sodec
New Member
3 years ago
Solved

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 ...
DAX tables
generateall
Group By
left join
  • johnt75's avatar
    johnt75
    3 years ago

    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]
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

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]