Forum Discussion
Power Bi Query that pulls from multiple tables (similar to an OR operator in SQL)
I have 5 tables in Power BI: Students, Class01, Class02, Class03, Class04. The Students table is the primary table and every student is registered in all of the ClassXX tables.
I’m trying to figure out how to write a Power BI query do the following. Basically, if a student answers “No” in any class, the query would set a field back in the Students table (based on their StudentID) to 1. If they didn’t enter a “No” in any class, nothing would happen. I’m getting the data from SharePoint 365 lists.
If Class01. Class01Answer =”No”
OR
If Class02. Class02Answer =”No”
OR
If Class03. Class03Answer =”No”
OR
If Class04. Class04Answer =”No”
THEN
Set Students.ClassAnswer=1
TABLES
Students
- StudentID
- ClassAnswer
Class01
- Class01ID
- Class01Date
- Class01Answer
- StudentID
Class02
- Class02ID
- Class02Date
- Class02Answer
- StudentID
Class03
- Class03ID
- Class03Date
- Class03Answer
- StudentID
Class04
- Class04ID
- Class04Date
- Class04Answer
- StudentID
Thank you very much!
You cannot do what you want. The reason is the selection of a value in a slicer does not trigger a new refresh of the dataset with that parameter.
You do have some options. You could (and probably should) combine the class tables into one. You'd need to add a column to each defining what table it is, then make sure all column names are the same. Then when they pick a value in a slicer, it only shows those records. This would also fit a Star Schema and it is what Power BI is designed for.
You could get more complex and bring in all 4 tables separately, then use a SWITCH() statement to dynamically calculate measures that would return calculations from the correct table, but I do not think that would perform well, and would be a pain to maintain if you had many measures and needed to add a 5th table for example. My first suggestion would just work once you added the 5th table to the Table.Combine() operation.
4 Replies
- edhans
Community Champion
You cannot do what you want. The reason is the selection of a value in a slicer does not trigger a new refresh of the dataset with that parameter.
You do have some options. You could (and probably should) combine the class tables into one. You'd need to add a column to each defining what table it is, then make sure all column names are the same. Then when they pick a value in a slicer, it only shows those records. This would also fit a Star Schema and it is what Power BI is designed for.
You could get more complex and bring in all 4 tables separately, then use a SWITCH() statement to dynamically calculate measures that would return calculations from the correct table, but I do not think that would perform well, and would be a pain to maintain if you had many measures and needed to add a 5th table for example. My first suggestion would just work once you added the 5th table to the Table.Combine() operation.
- edhans
Great Anonymous. See this article for starters, then this book to deep dive for Power BI modeling.
Please mark an answer as the solution so others can find it and know your question was resolved.