how to get first max or min value from table ?

[AnandRanga]

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 expression).

I can also set it to multi row card besides table,  sort it ascending or descending so that it will show sorted rows and will resize it like this:

but user can maximize it also using the icon on top right corner. Is it possible to remove that icon(customize the title bar icon) ?

Any solution? Thanks. 

[David4F]

Hi,

I'm pretty sure you are unable to disable the maximize option in the title bar.

Cheers

David

[Michiel · Accepted solution]

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].

[divergent]

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. 

[AnandRanga]

@Michiel

Thanks for your time but it's not working. Below query is returning blank-

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

this is my sql query:

select max(temp) from
(select count(distinct(Temperature)) as temp,[date]  from Second_18FE34D47A8F$
group by [date]
) as dd 

It's returning 71 and that's correct. how to get that in DAX?

[Michiel]

The measure is designed to return blank in most cases. Did you use it in a table with dates as row labels?

If I'm right, your SQL query returns only the max count (71), not the date when this number occurred. You should get that result with the MAXX formula I provided. The rest is done to retrieve also the date, but it only works when filtered to one date at a time.

[AnandRanga]

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 expression).

I can also set it to multi row card besides table,  sort it ascending or descending so that it will show sorted rows and will resize it like this:

but user can maximize it also using the icon on top right corner. Is it possible to remove that icon(customize the title bar icon) ?

Any solution? Thanks. 

[Greg_Deckler]

To my knowledge, no, there is no way to custimize that or get rid of that icon.