Need a dax formula to get the max count in a grouping

[Anonymous]

Hello!

I have the below table, and what I need, for a period, the max number of people on a day. For example:

Person	Day	Schedule
John	01/01/2021	Morning
Esther	02/01/2021	Evening
Maria	01/01/2021	Morning
Pedro	01/01/2021	Evening
John	02/01/2021	Morning

Outcome:

If I filter by all dates it will give me: 2. That is at some point I will need two people.

If I filter by only 1 January in the morning, it will return: 1, as in one person max needed.

In my mind is getting a MAXX that will be applied to a table, counting the rows and grouping dates and schedule. But don't know how.

Thanks in advance!

[Anonymous · Accepted solution]

At the end I combined MAXX, SUMMARIZE and DISTINCTCOUNT

[Anonymous]

The above is almost what I wanted. My mistake, it has to count distinct (I wish there was a COUNTDISTINCTX). For example at the bottom, for  Day 01/01/2021 and Morning, it's two unique people.

Person	Day	Schedule
John	01/01/2021	Morning
Esther	02/01/2021	Evening
Maria	01/01/2021	Morning
Pedro	01/01/2021	Evening
John	02/01/2021	Morning
John	01/01/2021	Morning

Any ideas?

[Icey]

Hi @Anonymous ,

Create two measures.

Measure = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Person] ),
    ALLEXCEPT ( 'Table', 'Table'[Day], 'Table'[Schedule] )
)

Measure 3 = MAXX('Table',[Measure])

Best Regards,

Icey

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

[Greg_Deckler]

@Anonymous Are you sure that you meant 1 January and not 2 January is when you need 2 people? Because...the data...

In any case, I think what you want is:

Measure = 
    VAR __Table = GROUPBY('Table',[Day],[Schedule],"__Count",COUNTX(CURRENTGROUP(),[Person]))
RETURN
    MAXX(__Table,[__Count])