Group based on max occurance of text field instead of max considering alphabetical letter

[travellers_173]

Hi,

New to Power BI and stuck on this. I am trying to get the max occured value while grouping the data for each month. In the below data for Jan Closed is more so I want the result group for ABC(Name), 1-Jan-23 (Month), Closed. But when I did a group by in Power query it is saying Open for Jan since O is max alphabetically. How to achieve this in Power Query (M) and no in Dax?

Name	Month	Status
ABC	01-Jan-23	Closed
ABC	01-Jan-23	Closed
ABC	01-Jan-23	Closed
ABC	01-Jan-23	Closed
ABC	01-Jan-23	Open
ABC	01-Feb-23	Open
ABC	01-Feb-23	Open
ABC	01-Feb-23	Closed
ABC	01-Feb-23	Closed
ABC	01-Feb-23	Closed
ABC	01-Feb-23	Open
ABC	01-Mar-23
ABC	01-Mar-23
ABC	01-Mar-23

[Anonymous]

Hi  @travellers_173 ,

Here are the steps you can follow：

1. Create calculated column.

Flag =
COUNTX(
    FILTER(ALL('Table'),
'Table'[Name]=EARLIER('Table'[Name])&&MONTH('Table'[Month])=MONTH(EARLIER('Table'[Month]))&&'Table'[Status]=EARLIER('Table'[Status])),
    [Name])

2. Create calculated table.

Table 2 =
DISTINCT(
FILTER(
    'Table',   
    'Table'[Flag]=
    MAXX(        FILTER(ALL('Table'),'Table'[Name]=EARLIER('Table'[Name])&&MONTH('Table'[Month])=MONTH(EARLIER('Table'[Month]))),[Flag])))

3. Result:

Best Regards,

Liu Yang

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

[lbendlin]

Please indicate what should happen in case of a tie (February)  and in case of missing status (March)

Note that Power Query is case sensitive and does not trim values.