Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
STEPHENC
Frequent Visitor

RANK value in Calculate Column with Filtered Values

I am new to this forum and need to ask a question related to RANKX function.

 

I have a table containing Multiple Records per Clients [SUBID]. These records each have a [DATE] and a [SERVICEID] and the combination of these three attributes is Unique. I need to create a Calculated Index (Rank?) Column that will Rank each SUBID record by [DATE] (first) and [SERVICEID]. Conceptually this should emulate the SQL :

 

rank() over (partition by SUBID order by DATE ASC, SERVICEID DESC)

 

I also need this Rank to be Calculated based on whatever record filters are enabled which may be daterange or any other column value.

 

Any help would be appreciated as I have been going round in circles for days

4 REPLIES 4
v-caliao-msft
Employee
Employee

@STEPHENC,

 

I have tested it on my local environment, we can use rankx and allexcept function to achieve this requirement.

Smaple data
Capture.PNG

Create two measures.
TotalAmount = SUM(Table2[Amount])

Rank = RANKX(ALLEXCEPT(Table2,Table2[SUBID],Table2[DATE]),Table2[TotalAmount])

Results.
Capture1.PNG

 

Regards,

Charlie Liao

Ashish_Mathur
Super User
Super User

Hi,

 

Share a dataset and show the expected result.

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/


Hi @v-caliao-msft, Thanks for your reply.

 

My problem is slightly different. My dataset represents transactions showing ServiceID, Date, ClientID and a bunch of other attributes per record. I am not interesetd in ranking by any value but I want to Rank transactions (Rows) per Client by Date. That is each client ID will have its transactions ranked by Date in a calculated column. The following DAX formula in fact does this

 

RANK =
          VAR
             TQB =CALCULATETABLE(IBRO_BB,ALLEXCEPT(IBRO_BB,IBRO_BB[CLIENT_ID]))
          RETURN

RANKX( TQB,IBRO_BB[DATE].[Date],,ASC)

 

(this is similar to SQL rank() with partitioning by CLIENT_ID and ordering by [Date]  for ALL records)

..

 

The problem is that if I have a FILTER/SLICER on the dates, the rank value does not recalculate only on the FILTERED/SLICED records. The two images below try to illustrate this. Column is the Calculated Column. The first image shows how I would like the records ranked and that works fine.

 

The second image shows how I would like the RANK to be calculated when I Filter/Slice February out. Column 1 shows how this actually appears and the "Column as Needed" is the result I want. Filtered out records should not be ranked.

 

 

 NoDateFilter.JPGWithDateFilter.JPG

Anonymous
Not applicable

Hey @STEPHENC,

Were you able to resolve this issue with dax, or any other method?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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