Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
I have tested it on my local environment, we can use rankx and allexcept function to achieve this requirement.
Smaple data
Create two measures.
TotalAmount = SUM(Table2[Amount])
Rank = RANKX(ALLEXCEPT(Table2,Table2[SUBID],Table2[DATE]),Table2[TotalAmount])
Results.
Regards,
Charlie Liao
Hi,
Share a dataset and show the expected result.
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.
Hey @STEPHENC,
Were you able to resolve this issue with dax, or any other method?
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |