Power Bi Query that pulls from multiple tables (similar to an OR operator in SQL)

[Anonymous]

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!

[edhans · Accepted solution]

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.

[Anonymous]

It took me awhile to combine the class tables into one successfully. I decided which columns were needed and made sure all column names were the same, per your directions. Thank you!

[Anonymous]

Thank you for giving me direction.  I will start with the first step of combining the classes into 1 table.  I will also review Star Schema.  I really appreciate your help!

[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.