Creating Filter Using Column Names

[MJEnnis]

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

[MJEnnis]

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!

[AlexisOlson · Accepted 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.

[MJEnnis]

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

[AlexisOlson]

I don't recommend unpivotting in DAX if you can avoid it. It's much cleaner and easier in the query editor.

[MJEnnis]

Student Levels is a calculated table. Is there a way to do it in the query editor (without first rebuilding Student Levels)?

[AlexisOlson]

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.