cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

Data Relationships

Hi all,

I need your help to resolve the issue.

I have two tables A and B.

Table A

|Name|Price|

|A|6|

|B|8|

|C|10|

Table B

|Name|Cost|

|A|1|

|C|2|

I created a relationship between Table A Name and Table B Name.

Cardinality Many to Many + cross filter Single (Table A filters Table B).

Now I want to create a table with the following columns:

|Name (Table A)| Price (Table A)| Cost (Table B)|

But, I don't see record B from Table A, probably due to the fact it doesn't exist in table B. Could you help me out here?

The ideal outcome, I should see record B with Cost 0.

Thanks a lot!

2 ACCEPTED SOLUTIONS
Super User

I would recommend creating a third table to be a bridge table like this:

Table3 =

DISTINCT(

UNION(

SELECTCOLUMNS('Table1',"Name",[Name]),

SELECTCOLUMNS('Table2',"Name",[Name])

)

)

Create your relationships to both of your tables and use this bridge table in your visuals for Name.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Community Support

Hi @mkornel ,

You can try to create a new calculate table:

``````Table =
'Table A',
"Cost",
VAR x =
LOOKUPVALUE ( 'Table B'[Cost], 'Table B'[Name], 'Table A'[Name] )
RETURN
IF ( ISBLANK ( x ), 0, x )
)``````

You will get the result like your expected:

Here is the demo, please try it: PBIX

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Community Support

Hi @mkornel ,

You can try to create a new calculate table:

``````Table =
'Table A',
"Cost",
VAR x =
LOOKUPVALUE ( 'Table B'[Cost], 'Table B'[Name], 'Table A'[Name] )
RETURN
IF ( ISBLANK ( x ), 0, x )
)``````

You will get the result like your expected:

Here is the demo, please try it: PBIX

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Super User

I would recommend creating a third table to be a bridge table like this:

Table3 =

DISTINCT(

UNION(

SELECTCOLUMNS('Table1',"Name",[Name]),

SELECTCOLUMNS('Table2',"Name",[Name])

)

)

Create your relationships to both of your tables and use this bridge table in your visuals for Name.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...