RANKX on the filtered view

[sohamChak]

Hi,

I have tried many approaches but failed to reach a solution.

So here goes the problem.

I want to find the rank dynamically based on the current selected date filter in the view. I want to find the rank partitioned by BugId and ordered by Pipeline for those rows that are present in the current filtered view.

I am using the following formula:

BugPipelineRank = RANKX( CALCULATETABLE(RawOfflineStats, ALLEXCEPT(RawOfflineStats, RawOfflineStats[BugId])), RawOfflineStats[Pipeline], RawOfflineStats[Pipeline],, Dense )

Detailed explanation from here. 

The result is as below

The above results does look correct until you apply a shorter date filter.

Now BugPipelineRank should be 1 in this case, but it is not. I understand that 'CALCULATETABLE(RawOfflineStats, ALLEXCEPT(RawOfflineStats, RawOfflineStats[BugId]))' is picking up the entire table. How should I modify the formula to pick up only the set of filtered rows?

I've tried several variations, but if someone can point me in the right direction I'd appreciate it.

Thanks.

[Floriankx]

Hello,

your ALLEXCEPT statement results in the same BugPipelineRank no matter which Date Filter you apply.

I don't think your CALCULATETABLE statement isn't necessary.

Please replace it with the TableName only instead of CALCULATETABLE and let us know.

Best regards.

[sohamChak]

No it did not work.

I am trying to achieve the DAX equivalent of the following SQL

SELECT
  EmailDate,
  Pipeline,
  BugId,
  DENSE_RANK() OVER( partition by BugId order by Pipeline) as BugPipelineRank
FROM
  RawOfflineStats

In the above query, RawOfflineStats is the complete table, I want to achieve the same on the filtered view in my report.

The solution you suggested will not do a partition by BugId.

[Floriankx]

Hello,

this should be fine:

BugPipelineRank = 
RANKX(RawOfflineStats, RawOfflineStats[Pipeline], RawOfflineStats[BugID],, Dense )

[sohamChak]

No it did not work.

As per the documentation you are trying to do

DENSE_RANK() OVER( partition by Pipeline, BugId) as BugPipelineRank

which is not the intention.

[ozne99]

Having the same problem. I have 

RANKX (FILTER(BUDGET, BUDGET[ID] =EARLIER(BUDGET[ID])), BUDGET[Time Stamp].[Date], ,DESC, Dense), but RANK does not change when I use my Time Stamp slicer.