Filtering associated values in Power BI

user_12668992 0
Viewed 80

Suppose you have a huge dataset with pupils and the classes that they attend. I want to create a filter where, when you filter on pupils, only the classes that they attend are shown. However, I also want to keep the other pupils who attend those indirectly filtered classes.

Example

Dataset:

Pupil ID Class ID
1 1
1 2
2 1
3 1
3 4
4 4
etc. etc

So when I filter on Pupil ID = 1, the output should be:

Pupil ID Class ID
1 1
1 2
2 1
3 1
etc. etc

My thought process so far is that each class is a group, and each group is overlapping. By choosing a Pupil ID, you indirectly choose a set of groups.

Does anyone have an idea as to how to avoid removing the non-selected pupils within the groups which are indirectly chosen by the selected (filtered) pupil?

Note: I have built the data model so that there are DIM_Pupil and DIM_Class tables.

dax powerbi
1 Answers

For the sake of simplicity, let's say that the data from your example above all lives in one table (Table). The problem is, if you're filtering based on Table[Pupil ID], then you're literally telling Power BI that the only thing you want to see is rows with the selected Pupil ID(s).

To work around this, you need to create a new table and associate it in such a way that you can filter how you would like. In your case, create a new table (ex. Filter), which looks the exact same as your original Table. Then, in Power BI's model tab, associate Table[Class ID] to Filter[Class ID] in a many-to-many relationship.

Table

Pupil ID Class ID
1 1
1 2
2 1
3 1
3 4
4 4
etc. etc

Filter

Pupil ID Class ID
1 1
1 2
2 1
3 1
3 4
4 4
etc. etc

Class ID <--> Class ID

Table Connection

Now all you need to do is display your data using the Table table and filter your data using the Filter[Pupil ID] column. This will give you the result that you want.

Note that this solution will need to be adjusted slightly based on your actual table structure, but the concept remains the same.

user_178844140
Related