Matrix to show Top item only - Text

[ryan_b_fiting]

Hello Community - 

I am looking to create a matrix visual that will show me the Top 1 item by each day and hour of the day.  I want the values to be text so that it shows me based on the day and hour intersection what are the users favorite drinks.  I have a table that contains the below data (favorite drink is from a users table joined to this table on user id).

I want to take this data and baseed on the number of visits (Row count of the above table) I want to show the top ranked favorite drink by day and hour of visit time.  So I would like my matrix to be similar to the one below, but instead of the counts I want to show the text for the Top ranked drink caregory:

I am thinking I have to do some sort of SUMMARIZE and RANKX function and then set the filter on the visual to RANK =1.  But I cannot figure it out nor can I get the correct output.  I seem to always just get the first (alphabetically) favorite drink on that day and time visisted.

Any help on this would be great.  I have been spinning my wheels for a while trying to figure this out.

Thanks Community!

Ryan F

[Greg_Deckler · Accepted solution]

You could just do a MAXX over a SUMMARIZE with a COUNTROWS in it to get your maximum amount of rows for a product. Then just do a MAXX over a FILTER of that same SUMMARIZE table to get the drink that corresponds to that maximum. You post has a lot of words that I didn't read but something like:

Measure =

VAR __Table =

  SUMMARIZE(

    'Table',

    [Favorite drink],

    "Count",COUNTROWS('Table')

  )

VAR __Max = MAXX(__Table,[Count])

VAR __Favorite = MAXX(FILTER(__Table,[Count] = __Max),[Favorite Drink])

RETURN

__Favorite

It is kind of like https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/td-p/985814

[ryan_b_fiting]

@Greg_Decklerworked just as needed.  Thanks for the quick answer!

[Greg_Deckler]

Great! 🙂