Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

DAX Max value from another column based on row value

I'm trying to write a DAX function to find the maximum value in one column based on a condition in another, but have this condition change dynamically based on the row value.   With this code...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    HI Anonymous

     

    Try this

     

    =
CALCULATE (
    MAX ( RankOfArea[count] ),
    ALLEXCEPT ( Rankofarea, Rankofarea[Line] )
)
JQuon's avatar
JQuon
Helper I
8 years ago

Is it possible to use this formula and only show where the Maximum is within the given row and all other rows show blank?  I would like to return an X in the row where the max is.  I need to do this so I can filter the data through a slicer for X and ignore blanks?

 

 

Type      Version   Max

Apple         1

Apple         3

Apple         8

Apple         9          x

Peach         1

Peach         10        x

Peach         7

Peach         4

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

JQuon

 

You can use this calculated column

 

Assuming your TableName  is Table1

 

MAx_Column =
IF (
    Table1[Version]
        = CALCULATE ( MAX ( Table1[Version] ), ALLEXCEPT ( Table1, Table1[Type] ) ),
    "X"
)
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    JQuon

     

    Also you can use a ranking technique...i.e. rank the versions for each type and then filter for RANK 1

    i.e use this calculated column

     

    RANK =
RANKX (
    FILTER ( Table1, Table1[Type] = EARLIER ( Table1[Type] ) ),
    Table1[Version],
    ,
    DESC,
    DENSE
)
  • JQuon's avatar
    JQuon
    Helper I
    8 years ago

    I think this worked! Thank you so much!

  • ara0530's avatar
    ara0530
    Advocate I
    8 months ago

    what if X was a numeric value and I want to filter those that are not zero?   This is not working on that scenario