How to make RANKX ignore column rows containing certain strings?

[matheus_peppers]

Hello, how are you?

I am creating a RANKX function, where it brings me the ranking of cities that had the most ad reach, however, I want in this function, to make RANKX not consider the lines that are written a certain string, such as "Unidentified City", as it is in my case.

Does anyone know how I can achieve this?

I have tried the following DAX formula and several others, but it is not working:

FB - Ranking Reach (cities) v2 =
RANKX (
    ALL ( dFacebook_AdSets[City] ),
    [FB - TOTAL Reach],
    NOT(
        FILTER(dFacebook_AdSets,dFacebook_AdSets[City] IN {"Unidentified City in Vale do Paraíba"}
    )
)
)

[johnt75 · Accepted solution]

You could try something like

FB Ranking =
VAR CitiesToIgnore = { "Unidentified City in Vale do Paraíba" }
VAR CitiesToRank =
    EXCEPT ( ALL ( dFacebook_AdSets[City] ), CitiesToIgnore )
RETURN
    RANKX ( CitiesToRank, [FB - TOTAL Reach] )

Just add more entries into the CitiesToIgnore variable

[matheus_peppers]

Could you explain me the logic behind this function you did? Sorry for the ignorant or even layman question, it's because I am new to DAX and want to master this language.

[johnt75]

The CitiesToRank variable will contain all the cities in your data except the ones you put into the CitiesToIgnore variable, those will be filtered out. The RANKX then iterates over the CitiesToRank variable, so only the cities which have not been excluded will be considered and given a ranking.

If you're new to DAX an haven't done so already, check out www.sqlbi.com. Everything on there is extremely helpful, and their Mastering DAX course is well worth taking if you can.