cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jimmcslim Member
Member

Parameters and limiting rows in related tables?

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Thejeswar Senior Member
Senior Member

Re: Parameters and limiting rows in related tables?

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

View solution in original post

3 REPLIES 3
Microsoft Phil_Seamark
Microsoft

Re: Parameters and limiting rows in related tables?

Hi @jimmcslim

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Thejeswar Senior Member
Senior Member

Re: Parameters and limiting rows in related tables?

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

View solution in original post

jimmcslim Member
Member

Re: Parameters and limiting rows in related tables?

 

@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!

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors