Forum Discussion

matheus_peppers's avatar
matheus_peppers
Frequent Visitor
3 years ago
Solved

How to make RANKX ignore column rows containing certain strings?

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 a...
  • johnt75's avatar
    johnt75
    3 years ago

    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

johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

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's avatar
    matheus_peppers
    Frequent Visitor
    3 years ago

    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's avatar
      johnt75
      Icon for Super User rankSuper User
      3 years ago

      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.