Data Relationships

[mkornel]

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!

[Greg_Deckler · Accepted solution]

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.

[v-yingjl · Accepted solution]

Hi @mkornel ,

You can try to create a new calculate table:

Table =
ADDCOLUMNS (
    '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.

[Greg_Deckler · Accepted solution]

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.