Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
What is best way to connect these table in model view?
TABLE A | |
11111 | A |
11111 | B |
22222 | A |
33333 | A |
33333 | C |
TABLE B | |
11111 | X |
22222 | Y |
33333 | Z |
TABLE C | |
11111 | XX |
11111 | ZZ |
11111 | ZZ |
22222 | ZZ |
22222 | AA |
33333 | XX |
33333 | BB |
33333 | CC |
Solved! Go to Solution.
Hi @stribor45
Normally, that's how we connect(make sure that the relationship is one-to-many or one-to-one):
Here are some links may help you:
Many-to-many relationship guidance - Power BI | Microsoft Learn
How to Create a Bridge Table in Power BI | phData
If you don't have a lot of data, merge the tables could be a better choice:
The result is as follow:
Merge queries overview - Power Query | Microsoft Learn
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @stribor45
This is because of the direction of the relationship.
For example, the Column1 from Table A cannot affect the result of COUNT(Table_B[ID]) is because of the direction of the relationship is from Table B to Table A(Table B can affect fields in Table A, but Table A cannot affect Table B, Table C is the same):
You can try to change the Cross-filter direction of the relationship to both, so that the Table A can affect the result:
Here for your reference:
Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @stribor45
Normally, that's how we connect(make sure that the relationship is one-to-many or one-to-one):
Here are some links may help you:
Many-to-many relationship guidance - Power BI | Microsoft Learn
How to Create a Bridge Table in Power BI | phData
If you don't have a lot of data, merge the tables could be a better choice:
The result is as follow:
Merge queries overview - Power Query | Microsoft Learn
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-zhengdxu-msft I did that but when I do
My_Count = COUNT(Table_B[ID])
This measure number is always the same no matter how I filter other visuals
Hi @stribor45
This is because of the direction of the relationship.
For example, the Column1 from Table A cannot affect the result of COUNT(Table_B[ID]) is because of the direction of the relationship is from Table B to Table A(Table B can affect fields in Table A, but Table A cannot affect Table B, Table C is the same):
You can try to change the Cross-filter direction of the relationship to both, so that the Table A can affect the result:
Here for your reference:
Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
table b is
I dont think there should be extraciton. I put those values very arbirtary just to show the data. x and xx are completly different things. I am sorry I should not have used thise values
TABLE A | |
11111 | apple |
11111 | pear |
22222 | apple |
33333 | apple |
33333 | peach
|
TABLE B | |
11111 | audi |
22222 | BMW |
33333 | Jeep |
TABLE C | |
11111 | history |
11111 | geography |
11111 | biology |
22222 | biology |
22222 | physics |
33333 | history |
33333 | english |
33333 | religion |
@stribor45 here no column is justifying the 1st and the basic rule of primary key, thats is a column(primary key) should be unique(distinct).
Please provide more data
I dont have any more. This is sammpe data i have. How would you connect it based on the data i provided?
extract number from tables and then make realtionship between tables
Table A (One) ---* Table B (Many) ---* Table C (Many)
NumericPrefix NumericPrefix NumericPrefix
That's what I can relate , because else everything is unique in all 3 tables.
If it helps Kudos to work and Accept it as Solution.
I am not sure I understand what you mean. would you be able to provide more explanation
Identifying Common Columns:
The key to connecting these tables lies in the initial numeric portion of each sequence (11111, 22222, 33333). This suggests a hierarchical relationship:
Level 1 (Numeric Prefix): 11111, 22222, 33333
Level 2 (Single Letter Suffix): A, B, X, Y, Z
Level 3 (Double Letter Suffix): XX, ZZ, BB, CC
Connecting the Tables in Power BI:
Extract the Numeric Prefix: In each table, create a calculated column to extract the numeric prefix. You can use DAX functions like LEFT or MID combined with FIND to achieve this. For example, in Table A:
Code snippet
NumericPrefix = LEFT(TableA[Column1], 5) // Assuming the sequences are in a column named 'Column1'
Do the same for Tables B and C.
Create Relationships:
Table A to Table B: Create a one-to-many relationship from Table A to Table B using the NumericPrefix column. Table A will be on the "one" side, and Table B will be on the "many" side. This assumes that each numeric prefix in Table A corresponds to one or more entries in Table B.
Table B to Table C: Similarly, create a one-to-many relationship from Table B to Table C using the NumericPrefix column. Table B will be on the "one" side, and Table C will be on the "many" side.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
142 | |
71 | |
64 | |
52 | |
50 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |