Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
sodec
New Member

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:

EmployeeIDCurrent ManagerCurrent OutputPrevious ManagerPrevious Output
1A10B5
2A20A15
3B30A

40

4B5B10

 

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

Current ManagerCurrent Output
A30
B35

And:

Previous ManagerPrevious Output
A55
B15

 

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 ManagerCurrent OutputPrevious OutputDelta Output
A3055-25
B351520

 

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
x2
1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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]

View solution in original post

2 REPLIES 2
sodec
New Member

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

johnt75
Super User
Super User

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]

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

November Power BI Update Carousel

Power BI Monthly Update - November 2025

Check out the November 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.