Apply a measure conditionally when data category changes

[aaronhowe]

I'm stuck again 😫

Previously I was asking about ranking values through DAX (see this thread) and have achieved what I wanted there, however I'm struggling with the complexity of the underlying data.  In my current model ranking data is applied against an entire table indiscriminately; however I'm going to need to sub-rank values according to changes.

So for example in the ranking data right now, all grades in the table are included in the rank, whereas the rank should really be applied according to person and month.  It would currently look like this:

[Table1]

[Name]            [MonthYear]            [Cost]            [Grade]            [Rank]
Joe Smith         January 2024           1000              A                  3
Joe Smith         January 2024           1000              A                  3
Joe Smith         January 2024           1000              B                  5
Andy Jones        January 2024           500               C                  7
Andy Jones        February 2024          500               F                  9
Sue Montague      January 2024           3000              A                  3
Sue Montague      February 2024          3000              B                  5
Sue Montague      February 2024          3000              C                  7
Sue Montague      March 2024             3000              D                  8

My question though is how could I apply that ranking function to Joe Smith in January 2024 alone, then repeat it for Sue Montague in February 2024 etc - where Joe's results would be a rank of 2/2/3 and Sue would be 1/2.  Is there a function - either in M or Dax - that will effectively group the Name and MonthYear fields here, apply the function, then reapply to the next applicable record?

[v-zhouwen-msft · Accepted solution]

Hi @aaronhowe ,

The Table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a column

Rank = 
VAR _a = [Name]
VAR _b = [MonthYear]
VAR _c = [Grade]
RETURN COUNTROWS(FILTER('Table','Table'[Name] = _a && 'Table'[MonthYear] = _b && 'Table'[Grade] <= _c))

2.Final output