cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Apply a measure conditionally when data category changes

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]

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?

1 ACCEPTED SOLUTION
Community Support

Hi @aaronhowe ,

The Table data is shown below:

1. Use the following DAX expression to create a column

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

2.Final output

Community Support

Hi @aaronhowe ,

The Table data is shown below:

1. Use the following DAX expression to create a column

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

2.Final output

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Find out what's new and trending in the Fabric Community.

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors