Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Ishmale
Helper I
Helper I

Using Switch Statement to dynamic change Table Data; error: The expression refers to multiple column

Hello Community,

I hope this request finds you all in good Spirits.

I am in need of some help. 

Purpose: 

I am creating a dashboard that will allow frontline supervisors to quickly check their deparment's Completed equioment training. This will show each memeber of their team and what equipment they have been trained on and allow them them to filter down to only see equipment types or individuals.

Sources:
Sharepoint List that houses all employees and equipment types. example below

Ishmale_0-1703102092552.png

Problem:

When Querying the data into the BI I can not slice the information by equipment type in one slicer due to the Equipment Types being Columns. I have tried to get arround this by setting up a second list with the equipment types listed in row form as seen below.

Ishmale_1-1703102276822.png

This way I can set equipment to a slicer.

 

The issue is as follows, I am using a switch statment to try and buid a table to that shows only the equipment selected in the slicer and if there is an "X" in the record from the main Sharepoint list and only share those people that are trained on that.

I have tried using the following DAX expression

Ishmale_2-1703103148472.png

 

Any comments or support is welcomed.

 

1 ACCEPTED SOLUTION
christinepayton
Super User
Super User

It sounds like you need to unpivot those columns in Power Query. You can duplicate your query to mak a dimension table out of it and then relate it back to your original records on the ID column. Remove all columns except the type columns and ID, then right click on ID and "unpivot other" - this will put all of the type columns into a single column. Then create a relationship between the tables, making sure the filter direction is "both" so that the dimension can filter the original table, and you should be able to slice on it. 

View solution in original post

3 REPLIES 3
Ishmale
Helper I
Helper I

Hello Christine,
I will give this a try and report back.

Thank you.

christinepayton
Super User
Super User

It sounds like you need to unpivot those columns in Power Query. You can duplicate your query to mak a dimension table out of it and then relate it back to your original records on the ID column. Remove all columns except the type columns and ID, then right click on ID and "unpivot other" - this will put all of the type columns into a single column. Then create a relationship between the tables, making sure the filter direction is "both" so that the dimension can filter the original table, and you should be able to slice on it. 

It worked, thank you!

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.