DAX to produce a Ranking -- not working as expected

[bvy]

Hi. I’m trying to produce a ranking in DAX that I can use in visual-level filters. I have two tables linked on VIN:

Cars

VIN | Color | Type

123 | Red | Ford

124 | Green | Ford

125 | Blue | Ford

201 | Blue | Mazda

202 | Blue | Mazda

203 | Blue | Mazda

204 | Black | Mazda

205 | Black | Mazda

206 | Black | Mazda

999 | Silver | Toyota

RepairsRecords

VIN | RecordID | Description

123 | 1 | Fixed brakes

123 | 2 | Fixed brakes again

124 | 3 | New tires

124 | 4 | Oil change

124 | 5 | Coolant flush

201 | 6 | Inspection

201 | 7 | Something else

999 | 8 | Engine rebuild

So there’s a one-to-many on VIN. Now I’d like to add some DAX to rank each vehicle type by the the number of repair records. It should update as different slicers (like color) are selected. So if I put this in a table, I get the following:

Type | Count(RepairRecords) | Ranking

Ford | 5 | 1

Mazda | 2 | 2

Toyota | 1 | 3

Here’s the DAX.

Ranking = RANKX(ALLSELECTED(Cars[Type]), CALCULATE(COUNT(RepairRecords[ID])))

It works if I make a table with just Type, Count(ID) and Measure (as illustrated above), but as soon as I add, say, Color, it starts associating other vehicle types with Ranking 1. Ranking should always be one-to-one with Vehicle.Type. Likewise, if I add say a pie chart and put a visual-level filter “where Ranking = 1” on it, it shows me other VehicleTypes as I start adding different fields to the visual.

How can I fix this? Thanks. 

[VahidDM]

Hi @bvy 

Try this measure:

Ranking =
RANKX (
    ALLSELECTED ( Cars ),
    CALCULATE ( COUNT ( RepairsRecords[ RecordID ] ) ),
    ,
    DESC,
    DENSE
)

Output:

1- with Color

2- just type

3- All Fields

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

Appreciate your Kudos🙏!!

[bvy]

Thanks for taking a look, but I need the rankings to always stick with the Type. In that dataset, Ford should always be 1, Mazda should always be 2, and Toyota 3. Those rankings should not change if you add other fields to the visual, or change filter selections...