Measure returning Name of Category at Max of Another Measure

[nerokasai]

Hi all,

I would like to create a measure that returns the name of the ticket type having the highest number of tickets (must obtain this count from a measure as I would like to add filters and use it for other calculations). 

How should I best approach this?

Here's a sample table:

Ticket ID	Ticket Type sold	Date sold
1225	A	02 Aug 2018
1122	B	09 Jul 2017
3251	A	12 Sep 2018
2456	A	12 Sep 2018
2857	C	31 Aug 2018
1698	D	27 Jul 2018
1818	D	13 Aug 2018
1244	B	13 Sep 2017

Here's a general format of the count measure I want to make:

TicketsCount = CALCULATE(DISTINCTCOUNT(Tickets[Ticket ID])+0,Tickets[Date sold]<=End_Date,Tickets[Date sold]>Start_Date)

where End_Date & Start_Date are variables referring to a measure that will be filtered out by a slicer on the report. 

I also have a slicer for each Ticket Type, let this refers to 'Tickets'[Ticket Type sold]. 

I have a stacked column chart where Axis = 'Tickets'[Ticket Type sold] and Values = 'Tickets'[Tickets Count]. I intend to sort this by descending order, thus the column (ticket type) with highest Tickets Count will be displayed first on the left.

I want to add a card displaying the measure [Ticket Type at maxTicketsCount], such that when highest column from the chart is Type A, the card will therefore displays "A" in the report. 

Please help! I've tried a combinations of MAXX, TOPN, etc. but nothing is working out so far. 

Thank you!

[amitchandak]

As long as Date is coming from date dimension joined with Tickets[Date sold] or filter is on Tickets[Date sold], you do not put filter for Tickets[Date sold]

This should work

TicketsCount = CALCULATE(DISTINCTCOUNT(Tickets[Ticket ID])+0)

The same is true ticket type.

Now for top ticket type. Take a card. Put ticket type on that. Got to visual level filter and again drag Ticket type on that. Choose Top N filter and put the value as  TicketsCount and N =1

[nerokasai]

That works for one of my issue, thanks!!

Is there a way to create a 'measure' that returns the same result?

I also have a custom visual which requires a "Measure" input to display as text. Unfortunately, I cannot by-pass this as I need it for displaying other customized data.

Thank you! 

[v-lili6-msft · Accepted solution]

hi  @nerokasai 

You could use this formula to get the measure

Ticket Type at maxTicketsCount = MAXX(TOPN(1,VALUES(Tickets[Ticket Type sold]),[TicketsCount],DESC),[Ticket Type sold])

Of course, you could use MINX too.

and here is sample pbix file, please try it.

Regards,

Lin

[nerokasai]

This is exactly what I need, @v-lili6-msft 

Thanks a lot!