cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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!:
Power BI Cookbook Third Edition (Color)

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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.