Creating a Ranking for measures - with visual filters

[Gladiator909]

Hello Everyone,

I have the following table:

Make	Dates	Criteria	Count
BMW	01/01/2022	broken door	10
BMW	01/01/2022	broken handles	20
Mercedes	01/01/2022	no lights	10
Mercedes	01/01/2022	no fire	20
toyota	01/01/2022	no heat	30
Nissan	01/02/2022	broken door	8
Nissan	01/02/2022	broken handles	5
Toyota	01/02/2022	no lights	23
BMW	01/02/2022	no fire	1
BMW	01/02/2022	no heat	0
Nissan	01/03/2022	broken door	41
Nissan	01/03/2022	broken handles	21
Honda	01/03/2022	no lights	10
toyota	01/03/2022	no fire	10
BMW	01/03/2022	no heat	11

And I would like to create a measure that ranks the above by criteria. Basically, something like this:

The DAX formula that I used to achieve the above "RANKING" measure is: 

RANKING = RANKX(ALLSELECTED('Table'), CALCULATE(sum('Table'[Count]), ALLEXCEPT('Table','Table'[Criteria])),,DESC,Dense)

Now this is working great, but I would like to add two filters to my dashboard namely, "Make" and "Dates"

When I put those two filters on that dashboard, I can't get the ranking to switch accordingly for example:

When I select january 2nd, I would like "no lights" to be ranked 1st but the ranking does not seem to budge.

 Same thing when I use the make slicer:

Any idea how I can modify my dax statement to make this work.

Any help is greatly appreciated

Thank You

[Anonymous · Accepted solution]

Hi  @Gladiator909 ,

Here are the steps you can follow：

1. Create measure.

Sum_Measure =
SUMX(
    FILTER(
    ALLSELECTED('Table 1'),'Table 1'[Criteria]=MAX('Table 1'[Criteria])),'Table 1'[Count])

Rank =
RANKX(
    ALLSELECTED('Table 1'),[Sum_Measure],,ASC)

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[MageVortex]

At a glance, I suspect that the Model has an issue with it where it isn't properly matching.  Are the dates from the same table, or a model that is linked? If so, check the model connection and make sure matches are happening the way you expect. 

**EDIT**  I re-read your post, I think I am incorrect. I suspect it is due to the use of allselected or allexcept, but I'm not well versed enough to diagnose. Sorry.