Best way go model these three tables

[stribor45]

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

[Anonymous · Accepted 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.

[Anonymous · Accepted 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.

[stribor45]

Hi @Anonymous 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

[Anonymous · Accepted solution]

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.

[stribor45]

table b is

[stribor45]

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

[elitesmitpatel]

@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]

Please provide more data 

[stribor45]

I dont have any more. This is sammpe data i have. How would you connect it based on the data i provided?

[elitesmitpatel]

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.

[stribor45]

I am not sure I understand what you mean. would you be able to provide more explanation

[elitesmitpatel]

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.