Reply
Ishmale
Helper I
Helper I
Partially syndicated - Outbound

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

Syndicated - Outbound

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

Syndicated - Outbound

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

Thank you.

christinepayton
Super User
Super User

Syndicated - Outbound

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. 

Syndicated - Outbound

It worked, thank you!

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

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

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)