Forum Discussion
how to get first max or min value from table ?
- 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].
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].
I am having similar trouble with a different dataset. I need the airline name (to be used on the card as 'Cheapest airline') based on the minimum price.
However, using your solution, I am only getting the price (count in this exampnle), while I want only the airline name as output (or Date in this example) filtered on price.