Forum Discussion

AnandRanga's avatar
AnandRanga
Icon for Helper III rankHelper III
9 years ago
Solved

how to get first max or min value from table ?

This is my table: I want to get only max value out of that, that is 71 and its date also and min value also. I am getting this table by distinct count of temperature filed.(without using expre...
  • Michiel's avatar
    9 years ago

    You're now using a simple measure created by just adding the column Temperature to your (output) table. The formula of this measure is (assuming the table containing the Temperature values is called MyTable):

    Count of Temperature = COUNT(MyTable[Temperature])

    To get only the date and the highest [Count of Temperature] value, you'll need to create a measure that only returns a value for the top date (in your example, 09/13/2016).

     

    First, create a measure to calculate the highest count:

    MaxCount = MAXX(ALL(Date[Date]), [Count of Temperature])

    Here, I assume your [Date] column is in a separate Date table. The formula above takes all date values, calculates [Count of Temperature] for each date, and returns the largest count. We need to use ALL here because you'll use this measure in the context of only one date, but need to consider all dates.

     

    Now, creating a measure that only returns a value for the top date can be done in different ways. The most simple one:

    MaxTemperatureCount = IF([Count of Temperature] = [MaxCount], [Count of Temperature])

    or alternatively:

     

    MaxTemperatureCount = CALCULATE([Count of Temperature], FILTER(Date[Date], [Count of Temperature] = [MaxCount]))

    The first one checks whether the current count is equal to the largest count, and only then returns a result. The second one filters the current context (of one day) to that day only if the count for that day equals the largest count (note we're not using ALL(Date[Date]) here, so for each row, the first argument to the FILTER function is a table with only one row).

     

    Both alternatives return results for each day with a count equal to the largest count; which may be more than one day. If you want to return, e.g. only a result for the last day with the largest count, you can build upon the FILTER expression to do that:

    MaxTemperatureCount = 
    CALCULATE(
        [Count of Temperature],
        TOPN(
            1, 
            FILTER(Date[Date], [Count of Temperature] = [MaxCount]),
            Date[Date])
            )
        )

    Here, TOPN returns only the top 1 row of the FILTER table with respect to the value of [Date].