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

The 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.

Reply
stribor45
Post Prodigy
Post Prodigy

Best way go model these three tables

What is best way to connect these table in model view?

 

TABLE A
11111A
11111B
22222A
33333A
33333C

 

TABLE B
11111X
22222Y
33333Z

 

TABLE C
11111XX
11111ZZ
11111ZZ
22222ZZ
22222AA
33333XX
33333BB
33333CC
2 ACCEPTED SOLUTIONS
v-zhengdxu-msft
Community Support
Community Support

Hi @stribor45 

 

Normally, that's how we connect(make sure that the relationship is one-to-many or one-to-one):

vzhengdxumsft_0-1735010720063.png

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:

vzhengdxumsft_1-1735010966886.pngvzhengdxumsft_2-1735010995664.png

The result is as follow:

vzhengdxumsft_3-1735011010446.png

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.

View solution in original post

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):

vzhengdxumsft_0-1735092044370.png

vzhengdxumsft_1-1735092399237.png

You can try to change the Cross-filter direction of the relationship to both, so that the Table A can affect the result:

vzhengdxumsft_2-1735092540488.png

vzhengdxumsft_3-1735092613464.png

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.

View solution in original post

11 REPLIES 11
v-zhengdxu-msft
Community Support
Community Support

Hi @stribor45 

 

Normally, that's how we connect(make sure that the relationship is one-to-many or one-to-one):

vzhengdxumsft_0-1735010720063.png

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:

vzhengdxumsft_1-1735010966886.pngvzhengdxumsft_2-1735010995664.png

The result is as follow:

vzhengdxumsft_3-1735011010446.png

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):

vzhengdxumsft_0-1735092044370.png

vzhengdxumsft_1-1735092399237.png

You can try to change the Cross-filter direction of the relationship to both, so that the Table A can affect the result:

vzhengdxumsft_2-1735092540488.png

vzhengdxumsft_3-1735092613464.png

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.

stribor45
Post Prodigy
Post Prodigy

table b is

stribor45
Post Prodigy
Post Prodigy

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
11111apple
11111pear
22222apple
33333apple
33333

peach

 

TABLE B
11111audi
22222BMW
33333Jeep
TABLE C
11111history
11111geography
11111biology
22222biology
22222physics
33333history
33333english
33333religion

 

@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). 

elitesmitpatel
Solution Supplier
Solution Supplier

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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