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
Anonymous
Not applicable

Problem with 3 data tables in a table chart

Hi everyone,

I have a problem.
I am importing my tables into power BI and create all the relationships.
Table A is connected to Table B via the personalnumber and it is a 1:n relationship.
Table A is also connected to Table C via a personalhaskey (created in the DWH) and it is also a 1:n relationship.
When I put a value from Table A and Table B in the table chart it is working. When I put something from Table A and Table C in the table chart it is also working.
But when I put something from Table A, B and C in the table chart I get an error message that Power BI can't determine the relationship between the columns.
But when I create two tables. One  with columns from Table A and Table B the other one with columns from Table A and Table C.
And I filter something from Table B the table (Table A and C) will also be filtered so Power BI is also finding connection between these two. But it is only filtered when both tables contain columns of Table A.

Does anyone have an Idea where my mistake is?
I also tested crosstablefiltering with the simple and both options but it made no difference.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Use column of table A as slicer and put the columns of Table B and C in a table visual. You can start your analysis from this point. You cannot put all A, B and C in a single visual.

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Is there a relationship between B and C. Can you paste a screenshot of the model or share the pbix file if possible ?

Anonymous
Not applicable

Hi there is no direct relationship between B and C.
They are only connected over table A.

Left table is Table B, middle one is Table A and right one is Table C.

Oradim_0-1614583079665.png
And in my table chart I want to have values from table A, B and C.

 

Anonymous
Not applicable

I can see you are trying a workaround to avoid many to many relationship. In such case we generally use the middle table as filter/slicer and it cannot be used with both wing table in a visual a the same time. Logically you can think that one PersonnalNumber from Table B has multiple values in Table C. How will Power BI know which data to show for which Id. 

You can go through this link for better understanding. Hope I was able to clarify the issue. Kindly accept this post as solution if it helped

Anonymous
Not applicable

Thank you.
You are right but I would be fine with a cartesian product.
Because there are no money values and it is just about Dataquality.
I am only analysing if a employee has a country, working contract, hour rate etc filled in the DWH.
And the table is just to verify the data and to see if there is "null" value and in which column and for which employee it is. I don't care if Power BI duplicates the data because it is not for analyzing it.
Is there an option in Power BI to see all the values?

Anonymous
Not applicable

Use column of table A as slicer and put the columns of Table B and C in a table visual. You can start your analysis from this point. You cannot put all A, B and C in a single visual.

Anonymous
Not applicable

Would this also work with more tables?
They are all connected to table A.
So I can put tables B-H in the visual and use A as a slice?
Do I understand it right. Table B and C would be in the same table visual?

Anonymous
Not applicable

It should work i guess. Try it once and let me know the result

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.