Skip to main content
cancel
Showing results for 
Search instead 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

Reply
aaronhowe
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]

[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?

1 ACCEPTED SOLUTION
v-zhouwen-msft
Community Support
Community Support

Hi @aaronhowe ,

The Table data is shown below:

vzhouwenmsft_0-1716429399712.png

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

vzhouwenmsft_1-1716429455448.png

vzhouwenmsft_2-1716429471894.png

 

 

View solution in original post

1 REPLY 1
v-zhouwen-msft
Community Support
Community Support

Hi @aaronhowe ,

The Table data is shown below:

vzhouwenmsft_0-1716429399712.png

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

vzhouwenmsft_1-1716429455448.png

vzhouwenmsft_2-1716429471894.png

 

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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