Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Need name of category with the most values

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!

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @Anonymous 

Please try

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

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

Hi @Anonymous 

Please try

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

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. 

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

Jihwan_Kim_0-1664987739986.png

 

 

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] )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.