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.
- Brian
eDNA Forum - Max Yes by Group Solution.pbix (22.5 KB)
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?
Thanks for your help!
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
Add a calculated column
ColumnValueUsingRank = if(RANKX(
FILTER(
‘myData’,
‘myData’[Field1]=EARLIER(‘myData’[Field1])
),
‘myData’[Field2]
) = 1, “Yes”, “No”)
Then use this calculated column to function as a slicer
This works!!! Thank you.