Joins and cardinality in PowerBI

[Anonymous]

Hi ,

How to specify cardinality for left outer join and right outer join in Power BI Desktop using datamodel(not using merge Queries in  QueryEditor)

[v-xuding-msft]

Hi @Anonymous ,

You could use the functions below to implement join tables. 

1. Left Outer: GENERATEALL, NATURALLEFTOUTERJOIN
2. Right Outer: GENERATEALL, NATURALLEFTOUTERJOIN
3. Full Outer: CROSSJOIN, GENERATE, GENERATEALL
4. Inner: GENERATE, NATURALINNERJOIN
5. Left Anti: EXCEPT
6. Right Anti: EXCEPT

For more details, you could reference the blog : Joining tables in Power BI with Power Query and DAX.

Best Regards,

Xue Ding

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

[Anonymous]

Hi @v-xuding-msft ,

Thanks for the reply.

But using any of the mentioned DAX Expressions will create one more table in the datamodel.

But this is not we want to achieve.

We need a left outer join when I tried to create a table visual in the report pane by selecting fields from 2 tables.

Is there a way to achieve this?

[v-xuding-msft]

Hi @Anonymous ,

Do you try the formula that I created? Is it what you want?  If it is, kindly mark the helpful answer as a solution if you feel that makes sense. More people will benefit from here. If you have any question, feel free to post here. 

Best Regards,

Xue Ding

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

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

I created a sample  that you can check if it is what you want.

Measure = CALCULATE(SUM('Right Table'[Sales]),FILTER('Right Table','Right Table'[ID] in VALUES('Left Table'[ID])))

If I misunderstand, please share some sample data and the expected output that can help us understand your requirement clearly.

Best Regards,

Xue Ding

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

[Anonymous]

Hi @Anonymous ,

This can be done using DAX expression NATURALLEFTOUTERJOIN

Please check link below for more details:

https://docs.microsoft.com/en-us/dax/naturalleftouterjoin-function-dax

Did I answer your question? Please Mark my post as a solution !

Regards,

Abhijeet

[Anonymous]

Hi @Anonymous ,

Thanks for the reply.

But using NATURALLEFTOUTERJOIN() will create one more table in the datamodel.

But this is not we want to achieve.

We need a left outer join when I tried to create a table visual in the report pane by selecting fields from 2 tables.

Is there a way to achieve this?

[tex628]

Go into the model view in PBI Desktop and create a relationtionship. 

When you have createt one you can double-click it to open a screen where you can toggle the settings. There you change both cardinality and crossfilter direction. 

[Anonymous]

Hi @tex628 ,

Thanks for the previous reply.

This is not giving either left outer or right outer join.