topic Re: Need name of category with the most values in DAX Commands and Tips

Need name of category with the most values

Anonymous — Wed, 05 Oct 2022 15:56:15 GMT

I have a table of clients and their genders, and I need to identify the gender with the most values, so from a table like this:

Client 1 - Male

Client 2 - Male

Client 3 - Female

Client 4 - Male

Client 5 - Male

Client 6 - Male

I need to produce the word "Male" as a measure, because there are 5 males and only 1 female. I can't use visual filters to achieve this.

Using RANK, I got Male = 1 and Female = 2, but I'm not sure how to take that output and produce the word "Male".

What is wrong with my approach? Thank you!

Re: Need name of category with the most values

Jihwan_Kim — Wed, 05 Oct 2022 16:36:15 GMT

Hi,

Please check the below picture and the attached pbix file.

expected result measure: = VAR _gendertablewithcounting = ADDCOLUMNS ( DISTINCT ( Data[Gender] ), "@count", CALCULATE ( COUNTROWS ( Data ) ) ) VAR _groupbymaxcounting = GROUPBY ( _gendertablewithcounting, Data[Gender], "@maxcount", MAXX ( CURRENTGROUP (), [@count] ) ) RETURN MAXX ( _groupbymaxcounting, Data[Gender] )

Re: Need name of category with the most values

tamerj1 — Wed, 05 Oct 2022 16:36:18 GMT

Hi Anonymous

Please try

Max Gender = MAXX ( TOPN ( 1, SUMMARIZE ( 'Table', 'Table'[Gender], "@Count", COUNTROWS ( 'Table' ) ), [@Count] ), 'Table'[Gender] )

Re: Need name of category with the most values

Anonymous — Wed, 05 Oct 2022 19:00:03 GMT

This worked great - I was able to even add a filter on to COUNTROWS to remove blanks from consideration. Thanks so much!

Didn't try the other option, that might work as well.