Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a situation basically like this.
In my model, I have a table which documents which language is a person's first, second, third, and (sometimes) fourth language. Note that Person ID1 is a unique ID that represents each observation of someone being added to the database for a new purpose, but Person ID2 represents multiple entries of the same person. Note further that Person 10 has experienced a change from the first entry to the second.
Student languages
Person ID1 | Person ID2 | L1 | L2 | L3 | L4 | Start Date | End Date |
1 | 10 | German | Italian | English |
| 01/01/2020 | 31/12/2020 |
2 | 20 | Italian | English | German |
| 01/01/2020 | 31/12/2020 |
3 | 30 | Ladin | German | Italian | English | 01/01/2020 | 31/12/2020 |
4 | 40 | English | German | Italian |
| 01/01/2020 | 31/12/2020 |
5 | 10 | German | English | Italian |
| 01/01/2021 | 31/12/2021 |
I have a reference table that lists all possible languages.
Languages
Language ID | Language |
1 | English |
2 | German |
3 | Italian |
4 | Ladin |
And the Languages table has connections to other important tables so that I can filter those tables (and reports based on them) by language. Something like the following.
Language exams
Exam ID | Person ID2 | Language ID | Exam Date | Exam Result |
1 | 10 | 3 | 01/02/2020 | PASS |
2 | 20 | 1 | 01/02/2020 | PASS |
3 | 30 | 2 | 01/02/2020 | PASS |
4 | 40 | 3 | 01/02/2020 | FAIL |
5 | 40 | 3 | 01/03/2020 | PASS |
6 | 10 | 1 | 01/04/2021 | PASS |
I would now like to create a new filter that filters all relevant tables/reports by first, second, third and fourth language, regardless of which language that is.
FILTER
L1 |
L2 |
L3 |
L4 |
My solution (conceptually) would be to create a new reference table like the following, and then use Person ID2, Language ID, and the time stamps (i.e., exam date is between start and end dates) in order to calculate the corresponding Filter column in Language Exams.
New Table
Person ID2 | Filter | Language | Start Date | End Date |
10 | L1 | German | 01/01/2020 | 31/12/2020 |
20 | L1 | Italian | 01/01/2020 | 31/12/2020 |
30 | L1 | Ladin | 01/01/2020 | 31/12/2020 |
40 | L1 | English | 01/01/2020 | 31/12/2020 |
10 | L1 | German | 01/01/2021 | 31/12/2021 |
10 | L2 | Italian | 01/01/2020 | 31/12/2020 |
20 | L2 | English | 01/01/2020 | 31/12/2020 |
30 | L2 | German | 01/01/2020 | 31/12/2020 |
40 | L2 | German | 01/01/2020 | 31/12/2020 |
10 | L2 | English | 01/01/2021 | 31/12/2021 |
10 | L3 | English | 01/01/2020 | 31/12/2020 |
20 | L3 | German | 01/01/2020 | 31/12/2020 |
30 | L3 | Italian | 01/01/2020 | 31/12/2020 |
40 | L3 | Italian | 01/01/2020 | 31/12/2020 |
10 | L3 | Italian | 01/01/2021 | 31/12/2021 |
10 | L4 |
| 01/01/2020 | 31/12/2020 |
20 | L4 |
| 01/01/2020 | 31/12/2020 |
30 | L4 | English | 01/01/2020 | 31/12/2020 |
40 | L4 |
| 01/01/2020 | 31/12/2020 |
10 | L4 |
| 01/01/2021 | 31/12/2021 |
This would be easy enough with a calculated column in Language Exams. However, I have muliple tables and reports to filter and would like to avoid adding a calculated column to each.
Any tips on creating such a filter?
Thanks
Solved! Go to Solution.
Adding a calculated column for L1/L2/L3/L4 to each relevant table seems like a way to go with minimal rebuilding, though you probably want to create a dimension table for that filter so you can apply it to multiple tables simultaneously.
In case @MJEnnis or anyone else is interested, I started setting up a star schema while thinking about this question. See attached.
PS, the data set is large and there are many tables and reports. So I am looking for an easy way to add this filter without have to rebuild anything that already works. Thanks!
Adding a calculated column for L1/L2/L3/L4 to each relevant table seems like a way to go with minimal rebuilding, though you probably want to create a dimension table for that filter so you can apply it to multiple tables simultaneously.
In case @MJEnnis or anyone else is interested, I started setting up a star schema while thinking about this question. See attached.
Makes sense. Most of the dimension tables are alread in the model. I just have to unpivot StudentLanguages. But coincindentally, I found this post by you elsewhere which would help me unpivot the Student Languages table (very neat trick!): powerbi - Is it possible to unpivot in Power BI using DAX? - Stack Overflow
I don't recommend unpivotting in DAX if you can avoid it. It's much cleaner and easier in the query editor.
Student Levels is a calculated table. Is there a way to do it in the query editor (without first rebuilding Student Levels)?
This would be one of the cases where it isn't easy to avoid it. 🙂
You can't pull calculated tables into the query editor.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |