Forum Discussion

LRFeltz's avatar
LRFeltz
Advocate III
9 years ago
Solved

DAX function similar to the SELECT MAX (SQL)

Hello,

I would like some help
How do I search for a value and a certain date?

 

I need to do something similar to a SQL SELECT MAX.

What function can be used?

 

Thank you,
LRFeltz

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi LRFeltz,

     

    Suppose there is a table containing three columns (Column1,Column2,Column3), now you need to get the max value in Column2 and its corresponding date.

     

    In this scenario, you can use SelectColumns function to return the two columns you need first.

    NewTable=SelectColumns('TableName',"Date",'TableName'[Column1],"Value",'TableName'[Column2])

     

    Then use Max function to get the max value based on above table.

    NewTable2=CalculateTable('NewTable',FILTER('NewTable','NewTable'[Date]=MAX('NewTable'[Date])))

     

    Then, you can drag a table visual to display the data records in NewTable2.

     

    If I have something misunderstood, please share your sample data.

     

    Thanks,
    Yuliana Gu

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    Hi LRFeltz,

     

    Suppose there is a table containing three columns (Column1,Column2,Column3), now you need to get the max value in Column2 and its corresponding date.

     

    In this scenario, you can use SelectColumns function to return the two columns you need first.

    NewTable=SelectColumns('TableName',"Date",'TableName'[Column1],"Value",'TableName'[Column2])

     

    Then use Max function to get the max value based on above table.

    NewTable2=CalculateTable('NewTable',FILTER('NewTable','NewTable'[Date]=MAX('NewTable'[Date])))

     

    Then, you can drag a table visual to display the data records in NewTable2.

     

    If I have something misunderstood, please share your sample data.

     

    Thanks,
    Yuliana Gu