Help with Filtering & Slicer

[jereaallikko]

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 the floor level (from Topic column). So basically, I would like the slicer to contain Basement, Ground Floor, 2nd Floor & 3rd Floor. And with it, to show the primary topic (floor level) and sub-topics in a report graphs (f.ex. choosing Ground floor from slicer -> Ground Floor, Kitchen, Living Room, Bathroom & Washroom appears in a graph).

The filtering would work with Level ID column in a wanted way, but that is not the column I want to use as a slicer.

My real dataset contains over 200 rows with 70 primary topics and 133 sub-topics and it grows nearly everyday, so is there a simple way to solve the problem? Wouldn't want to modify my dataset everytime new data appears through refresh.

Thanks,

Jere

[jereaallikko]

Hi @amitchandak & @Greg_Deckler, thanks for your replies.

The issue was not explained very clearly, my bad. To rephrase it, I would simply need a new column, indicating the floor level. It could be done manually with conditional column based on Level ID column, where

Level ID column -> equals -> BM00 = Output Basement

Level ID column -> equals -> GF101 = Output Ground Floor

Level ID column -> equals -> F2.0 = Output 2nd Floor

Level ID column -> equals -> F3.0 = Output 3rd Floor

But my real dataset contains over 200 rows, and more appears almost everyday. The amount of rooms belonging to each floor level is irregular. So is there a simple code or DAX expression where I could create a new column based on the Level ID?

This is what Im looking for. Hopefully now I explained it a bit better 😄 Feel free to ask more explination if needed.

-Jere

[Greg_Deckler]

@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]

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?

[Greg_Deckler]

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

[mussaenda]

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.

[Anonymous]

create a calculated column:

FloorDetails=SWITCH([Level ID],
"BM00" ,"Basement",
"GF101" ,"Ground Floor",
"F2.0" ,"2nd Floor",
"F3.0" ,"3rd Floor","unidentified")

[amitchandak]

@jereaallikko , nor very clear. but refer if these two can help

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-february-2020-feature-summary/#_Hierarchic...

https://www.youtube.com/watch?v=cyOquvfhzNM

Can you please explain with an example.

Appreciate your Kudos.

[Greg_Deckler]

@jereaallikko - I am not entirely clear on the requirement here but it would seem that in one way or another you would need to define what is above and below. You could then implement a Complex Selector: https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534