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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ellengrace2293
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
Anonymous
Not applicable

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]
)

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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]
)

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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