Conditional statement

What dax would allow you to code the max for a series of repeated items as ‘yes’?20200504 Enterprise DNA Q1.xlsx (8.8 KB)

If I understand your question correctly, this should work for you:

``````Max Yes by Group =

CALCULATE(
MAX( 'Table'[Field2]),
FILTER(
'Table',
'Table'[Code] = "yes"
),
ALLEXCEPT(
'Table',
'Table'[Field1 ]
)
)
``````

I hope this is helpful. Full solution file attached below.

Is the question to look at the Yes and identify the highest Field2 Value or is it to look at field 1 and field 2 and assign yes to the row that corresponds to the highest field2 value.

This is what i came up with. Will require a couple of enhancement to the Dax to display Yes and No against the corresponding Field1 & Field2 value

Max Field2Value =
VAR MaxField2 =
CALCULATE (
MAX ( ‘myData’[Field2] ),
ALLEXCEPT ( ‘myData’, ‘myData’[Field1 ] )
)
RETURN

``````CALCULATE (
MAX ( 'myData'[Field2] ),
FILTER (
ALLEXCEPT ( 'myData', 'myData'[Field1 ] ),
'myData'[Field2] = MaxField2
)
)
``````

The beauty of DAX, so may elegant ways of accomplishing a task.

Hi C94, it would be the second, “look at field 1 and field 2 and assign yes to the row that corresponds to the highest field2 value”. I’ll test it out and let you know the outcome.

i made some further enhancement so it gives you the yes and no

``````MaxField2Value =
VAR MaxField2 =
CALCULATE (
MAX ( 'myData'[Field2] ),
ALLEXCEPT ( 'myData', 'myData'[Field1] )
)
VAR SelectedField2Value = SELECTEDVALUE(myData[Field2])
RETURN
if(ISFILTERED(myData[Field1]),  if (SelectedField2Value = MaxField2,"Yes", "No"), Blank())
``````

Hi @llchoong, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!

Hi C94, I replicated the same table and dax but ‘MaxField2Value’ is blank. Then I found out you used New Measure, and I was thinking New Column. When I switched the dax to new measure “yes” and “no” showed. How can you set it up as a “yes” “no” slicer or filter?

New Column

New Measure

I

Hi Brian, the “yes” and “no” are the unknowns. I would like to establish the “yes” & “no” and to be able to filter by that. A column would be useful. Otherwise, a method to create a filter using calculated measure works too. Let me know. Thanks!

So if you want to use it as a slicer, this is what i will do