How do you count the max and min in a column but you return the categorical data

[Anonymous]

Hey I have this table "Activities" with a column [ActivityType]. There are several different activities like "email" and "meeting". I want to have a count of what activity type is max and min and return what activity it is... not just the count. 

Right now I am doing these measures: 

Countactivity= COUNTROWS

MaxActivity= MAXX(VALUES(Activities[ActivityType]),[countactivity])

MinActivity = MINX (VALUES(Activities[ActivityType]), [countactivity]) 

I am getting 66K for the max and 108 for the min. How do I display what activity is associated to the max and min? 

[v-xiaotang · Accepted solution]

Hi @Anonymous 

Thanks for reaching out to us.

you can try this,

Count = CALCULATE(COUNTROWS(Activities),ALLEXCEPT(Activities,Activities[ActivityType]))

MaxActivity = 
var _max=MAXX(Activities,[Count])
return MAXX(FILTER(Activities,[Count]=_max),[ActivityType])

MinActivity = 
var _min=MINX(Activities,[Count])
return MAXX(FILTER(Activities,[Count]=_min),[ActivityType])

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , I have this code for date, but it should help in your case too

https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee884e

Use min in place of max for Minimum value