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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.