Forum Discussion

ellengrace2293's avatar
ellengrace2293
Frequent Visitor
8 years ago
Solved

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.

  • Anonymous's avatar
    Anonymous
    8 years ago

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      ellengrace2293
      Frequent Visitor
      8 years ago

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