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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
newkingdom
Frequent Visitor

How to Find either the max or min using related

I have 2 tables Payment and Sales.

The Payment table has Payment and Write-Off columns that I want to add to the Sales table.

Now the issue is the amounts could be either positive or negative.

Relationship is Payment(many) ---> Sales (one)

 

So I know I have to use the relatedtable. I added the field to the Sales table using the following:

maxx(RELATEDTABLE(Payment), Payment[Write-Off Amount])

 

But now I am not sure how to change the code so that ifthe Write-Off amount is positive instead of grabbing the max, the code will grab the min and when it is positive it will grab the max? Is this even possible in Power BI?

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

 

Measure =
VAR _sum = SUM(Payment[Write-Off Amount]))
VAR _maxx = MAXX(RELATEDTABLE(Payment),Payment[Write-Off Amount])
VAR _minx = MINX(RELATEDTABLE(Payment),Payment[Write-Off Amount])
RETURN IF(_sum<0,_minx,_maxx)

 

@newkingdom  can you try the above?

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

1 REPLY 1
smpa01
Super User
Super User

 

Measure =
VAR _sum = SUM(Payment[Write-Off Amount]))
VAR _maxx = MAXX(RELATEDTABLE(Payment),Payment[Write-Off Amount])
VAR _minx = MINX(RELATEDTABLE(Payment),Payment[Write-Off Amount])
RETURN IF(_sum<0,_minx,_maxx)

 

@newkingdom  can you try the above?

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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