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

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 Solution Sage
Solution Sage

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 Solution Sage
Solution Sage

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

Highlighted
jimmcslim Helper III
Helper III

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors