RANK value in Calculate Column with Filtered Values

[STEPHENC]

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

[v-caliao-msft]

@STEPHENC,

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

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.

[STEPHENC]

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.

 

[Anonymous]

Hey @STEPHENC,

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