Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to 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.
Hi @Anonymous ,
You could use the functions below to implement join tables.
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.
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?
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.
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.
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
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?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
101 | |
69 | |
69 | |
43 |
User | Count |
---|---|
146 | |
106 | |
105 | |
90 | |
65 |