Forum Discussion

jereaallikko's avatar
jereaallikko
Helper III
5 years ago

Help with Filtering & Slicer

Hello all,  I have slight problems with filtering the report in a wanted way. I have created a Power BI table to visualize my problem;     What I am looking for, is to use a slicer based on...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

jereaallikko - If I understand you correctly, you could do this:

Column =
  SWITCH('Table'[Level ID],
    "BM00","Basement",
    "GF101","Ground Floor",
    "F2.0","2nd Floor",
    "F3.0","3rd Floor",
    "Unknown"
  )

That's DAX. In Power Query:

if [Level ID] = "BM00" then "Basement" else if [Level ID] = "GF101" then "Ground Floor" else if [Level ID] = "F2.0" then "2nd Floor" else if [Level ID] = "F3.0" then "3rd Floor" else "Unknown"

 

jereaallikko's avatar
jereaallikko
Helper III
5 years ago

Hi Greg_Deckler Anonymous & mussaenda 

thanks for the replies.

 

Thanks for the help, that's what I'm looking for. But the real dataset contains more than 200 rows. I could do it like that, but the problem is that there is new data coming in continuously with different numbers and "Level IDs", so each time it happens, I should manually make adjustments to the column code. Is there any other/easier way to do it, so that I wouldn't have to make adjust afterwards?

  • mussaenda's avatar
    mussaenda
    Community Champion
    5 years ago

    Hi jereaallikko,

     

    if that's the case,

    you can use Text.Contains in power query.

     

    Like:

     

    if Text.Contains(Text.Upper([Level ID]), "BM")

    then "BASEMENT"

    else

    (your next condition)

     

    So everytime you will have new data to come and it detects that there is BM on your Level ID,

    it will directly call it as basement. You can also add AND on your condition to get what you rreally wanted.

     

    You need to identify their common denominator then you are good to go. insted of using the exact Level ID.

     

    Hope this helps!

     

     

    by the way, I used Power Query here.

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    jereaallikko - Not sure how you fool proof undefined future values for things.