Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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] )
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.