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] )
)
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

HI Anonymous

 

Try this

 

=
CALCULATE (
    MAX ( RankOfArea[count] ),
    ALLEXCEPT ( Rankofarea, Rankofarea[Line] )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi  ,

     

    Thanks for your reply.

     

    Why did you change the post title please? I don't want to return a running total, rather the maximum value.

     

    Edit - sorry that worked, my typing. No idea why. Thanks.

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      Hi Anonymous

       

      :smileysurprised:I didn't change the post title:smileysurprised::smileysurprised:

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi Zubair,

         

        Oh sorry, that was me assuming - I saw you were a power user !

         

        Must've been admin. Anyway cheers again for your response - didn't understand it but it worked :)

  • 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
)