Get Minimum and Maximum of Count

[Anonymous]

I have a categorical feature.

I want to get the maximum count and minimum count of categories in a column. That is, highest and and lowest counts (range).

How can I do that?

Here is an example:

Category

AAA

AAA

BBB

BBB

AAA

BBB

AAA

CCC

Maximum count is related to AAA which is 4

Minimum count is related to CCC which is 1.

I want to get 4 and 1 in two dax measures.

Thanks.

[Jihwan_Kim]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

https://www.dropbox.com/s/qcc42kg4ste44dr/Mah65.pbix?dl=0 

Min Count Category =

MINX (

GROUPBY (

ALL ( 'Table' ),

'Table'[Category],

"@maxcount", SUMX ( CURRENTGROUP (), 1 )

),

[@maxcount]

)

Max Count Category =

MAXX (

GROUPBY (

ALL ( 'Table' ),

'Table'[Category],

"@maxcount", SUMX ( CURRENTGROUP (), 1 )

),

[@maxcount]

)

Hi, My name is Jihwan Kim.

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

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Ashish_Mathur · Accepted solution]

Hi,

Try these measures:

Count = COUNTROWS(Data)

Maximum count = MAXX ( VALUES(Data[Category]), [Count] )

Minimum count = MINX ( VALUES(Data[Category]), [Count] )

[Anonymous]

So this worked for me. But I am wondering if there is a way to alter it. I have a table Activities that have a column [ActivityType]. I was able to count the max and min activies but I want it to return what that activity is. For example "email" is the max activity, how do I return that instead of the count? 

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file and show the expected result. 

[Anonymous]

Thank you very much.

I don't know why it didn't fully work with my measures and filters and data. Probably, because I had some null values in the main data. This modified version solved my problem. 

Count = COUNTA('Data'[Category])

The rest is the same.

[Ashish_Mathur]

You are welcome.

[danextian]

Hi @Anonymous ,

Try these:

MaxCount =
MAXX (
    SUMMARIZE (
        'Table',
        'Table'[Category],
        "count",
            CALCULATE (
                COUNTA ( 'Table'[Category] ),
                ALLEXCEPT ( 'Table', 'Table'[Category] )
            )
    ),
    [count]
)


MinCount =
MINX (
    SUMMARIZE (
        'Table',
        'Table'[Category],
        "count",
            CALCULATE (
                COUNTA ( 'Table'[Category] ),
                ALLEXCEPT ( 'Table', 'Table'[Category] )
            )
    ),
    [count]
)