cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ellengrace2293 Frequent Visitor
Frequent Visitor

Using a Max value in a IF statement for graph

Hello! Let's say I have a query with the following columns: [Date], [ID], [Flow], and [Overflow]. I need to have another column (or measure) that mostly equals the [Flow] value, except when [Flow] for that row equals the max [Flow] for that [ID] -- and when this is the case, this new column should equal [Overflow] only if [Overflow is greater than [Flow].

 

I've tried this:

measure >> Max Flow = MAX('Flow Data'[Flow])

calculated column = IF('Flow Data'[Flow] = Max Flow, IF('Flow Data'[Overflow] > 'Flow Data'[Flow], 'Flow Data'[Overflow], 'Flow Data'[Flow]), 'Flow Data'[Flow])

 

When I create this calculated column and add it to a graph filtered for one [ID] with the [Date] as the x-axis, it calculates the max value for each timestamp (which is just one value anyway). I need the new measure to respect the visual level filter and use the max for the whole [ID] in the IF statement for each row.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Ross73312 Super Contributor
Super Contributor

Re: Using a Max value in a IF statement for graph

This calculated column is what you were trying to write 

calculated column = 
var currentID = [ID]
var maxFlow = CALCULATE(
	MAX('Flow Data'[Flow]),
	ALL('Flow Data'),
	'Flow Data'[ID] = currentID
)
RETURN
IF(
	AND(
		[Flow] = maxFlow,
		[Overflow] > [Flow]
	)
	[Overflow], 
	[Flow]
)

   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


2 REPLIES 2
Highlighted
Ross73312 Super Contributor
Super Contributor

Re: Using a Max value in a IF statement for graph

This calculated column is what you were trying to write 

calculated column = 
var currentID = [ID]
var maxFlow = CALCULATE(
	MAX('Flow Data'[Flow]),
	ALL('Flow Data'),
	'Flow Data'[ID] = currentID
)
RETURN
IF(
	AND(
		[Flow] = maxFlow,
		[Overflow] > [Flow]
	)
	[Overflow], 
	[Flow]
)

   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


ellengrace2293 Frequent Visitor
Frequent Visitor

Re: Using a Max value in a IF statement for graph

Thank you for the quick answer! That worked great. I have a lot to learn about DAX.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 283 members 2,986 guests
Please welcome our newest community members: