Skip to main content
cancel
Showing results for 
Search instead 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

Reply
nerokasai
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 IDTicket Type soldDate sold
1225A02 Aug 2018
1122B09 Jul 2017
3251A12 Sep 2018
2456A12 Sep 2018
2857C31 Aug 2018
1698D27 Jul 2018
1818D13 Aug 2018
1244B13 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

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

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.

View solution in original post

4 REPLIES 4
amitchandak
Super User
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

 

Screenshot 2020-02-01 00.28.27.png

 

 

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! 

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

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.

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

 

Thanks a lot!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors