cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Measure returning Name of Category at Max of Another Measure

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!

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User

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

Frequent Visitor

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!

Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

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

Thanks a lot!