Using a Max value in a IF statement for graph

[ellengrace2293]

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.

[Anonymous · Accepted solution]

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

[ellengrace2293]

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