Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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?"
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?"
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |