Forum Discussion
Help with Filtering & Slicer
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
create a calculated column:
FloorDetails=SWITCH([Level ID], "BM00" ,"Basement", "GF101" ,"Ground Floor", "F2.0" ,"2nd Floor", "F3.0" ,"3rd Floor","unidentified")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"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?
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.
