Parameters and limiting rows in related tables?

[jimmcslim]

Is it possible to use parameters in the following way? (Assuming Import mode).

1. Master table (e.g. CustomerId, CustomerCode) is filtered to have only one row where the row matches the value of a parameter (e.g. CustomerCode = 'X'). This works.

2. Child table is related to master via CustomerId (e.g. Child table has ChildId, CustomerId, ChildData columns).

3. I expect Child table to automatically only import the rows where CustomerId equals the CustomerId of the row on the Master table that is included in the filter, but it seems that the filter on the Master table doesn't impact rows in the child tables?

[Thejeswar · Accepted solution]

Hi @jimmcslim,

Yes that's true. I have faced this scenario before also quite a couple of times.

I guess this is because of the order of precedence of the activities happening in the power bi.

i.e. Your relationships are built even before your filter based on parameter is applied

To solve this just merge the parent table with child table and expand only the columns from the parent table that you need and/or on which you want to apply the filter. Once expanded, apply the filter based on parameter to that column.

By merging, you solve two things.

1. You can make the datasets to only one instead of two 

2. If at all you feel that you want the tables to be separate, then this merger will help you with just filtering the child table based on parameter

[jimmcslim]

@Phil_Seamark: I am enquiring about what if parameters.

@Thejeswar : Yes, after a little further exploration this seems to be my solution. The Query Editor identifies the child table as being related to the parent table and offers to expand some or all of those columns into the child table. Then I can implement the same row filter on the child table as is present on the master table.

It's a little odd that it works this way, and would be good if there was a way to say to Power BI to filter out related rows at the dataset level. But I can achieve my objective now I think!

[Phil_Seamark]

Hi @jimmcslim

What do you mean by parameter?  Do you mean a filter set by a slicer, or the what if parameter?