Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
clairermarsh
Frequent Visitor

Relationships between data tables connected by a lookup table

I'm pretty new to Power BI (desktop) and have a question about relationships please find details below;

 

I have two data tables 

Data Table A                                                             Data Table B

Hours Worked      Amount        Gender                 Hours Worked      Amount        Role

10 hours                 10                   Male                     10 hours                 15                  Manager

20 hours                 11                   Male                     20 hours                   4                  Manager

30 hours                 17                   Male                     30 hours                 18                  Manager

10 hours                 16                   Female                 10 hours                 16                   Executive

20 hours                   8                   Female                 20 hours                 10                   Executive

30 hours                 15                   Female                 30 hours                 13                    Executive

 

As the Hours Worked columns contain duplicates I created a lookup table

Lookup Table C

Hours Worked      

10 hours                 

20 hours                 

30 hours                 

 

Table C has a One to many relationship with Tables A and B with the Cross filter direction set to Both in each case.

 

I set up two table visuals one for Table A and one for Table B (with all their respective columns in the table), I then create a slicer with Table C.

 

So far so good, for example I select 10 hours from the slicer and both table visuals are affected in the way one would expect. Now here is my problem (with all visual filters cleared) I want to be able to click on Female in Table A and have the values in Table B change but nothing happens.

 

I feel like I've missed a step here possibly in the way my data is structured. I see some amazing Power BI dashborads out there were all the visuals interact with each other but I know in the back ground they are driven by several different tables. I'm going for some training soon but this has been bugging me so any help would be appreciated.

 

Please let e know if you need any further info.

Cheers!

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

The reason that it appears nothing is happening is this:

 

You click on "Female" from table A.  That filters Table A where [Gender] = Female.  Looking at your data, you have the following values in Table A's filter context for [Hours Worked]:  10 Hours, 20 Hours, and 30 Hours.

 

Those three values then travel across the bi-directional filter to Table C, which says the only values that should stay in the filter context are 10 Hours, 20 Hours, and 30 Hours.

 

Those 3 values then travel across the other bi-directional filter to Table B, and filter the table to only show those 3 values.

 

As you can see, Table B doesn't appear to be filtered, because all three values stay in the filter context.

 

What you are really asking Power BI when you click "Female" is this:

 

"what Hours Worked values show up in Table B that also show up in Table A when [Gender] = Female?"

View solution in original post

1 REPLY 1
Anonymous
Not applicable

The reason that it appears nothing is happening is this:

 

You click on "Female" from table A.  That filters Table A where [Gender] = Female.  Looking at your data, you have the following values in Table A's filter context for [Hours Worked]:  10 Hours, 20 Hours, and 30 Hours.

 

Those three values then travel across the bi-directional filter to Table C, which says the only values that should stay in the filter context are 10 Hours, 20 Hours, and 30 Hours.

 

Those 3 values then travel across the other bi-directional filter to Table B, and filter the table to only show those 3 values.

 

As you can see, Table B doesn't appear to be filtered, because all three values stay in the filter context.

 

What you are really asking Power BI when you click "Female" is this:

 

"what Hours Worked values show up in Table B that also show up in Table A when [Gender] = Female?"

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.