Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |