- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
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.
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
Any comments or support is welcomed.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hello Christine,
I will give this a try and report back.
Thank you.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

It worked, thank you!

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
06-13-2024 07:52 AM | |||
05-24-2024 05:26 AM | |||
12-08-2023 03:14 AM | |||
01-06-2020 06:55 AM | |||
08-14-2018 03:46 AM |
User | Count |
---|---|
141 | |
117 | |
80 | |
65 | |
47 |