Forum Discussion

Gladiator909's avatar
Gladiator909
Helper I
2 years ago
Solved

Creating a Ranking for measures - with visual filters

Hello Everyone,

 

I have the following table:

 

MakeDatesCriteriaCount
BMW01/01/2022broken door10
BMW01/01/2022broken handles20
Mercedes01/01/2022no lights10
Mercedes01/01/2022no fire20
toyota01/01/2022no heat30
Nissan01/02/2022broken door8
Nissan01/02/2022broken handles5
Toyota01/02/2022no lights23
BMW01/02/2022no fire1
BMW01/02/2022no heat0
Nissan01/03/2022broken door41
Nissan01/03/2022broken handles21
Honda01/03/2022no lights10
toyota01/03/2022no fire10
BMW01/03/2022no 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

 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

2 Replies

  • MageVortex's avatar
    MageVortex
    Helper I
    2 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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