Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
sivashankr
Helper II
Helper II

LEFT OUTER JOIN in Modelling (Not Merging or DAX)

Hello Team,

 

By SQL looks like below. Could you let me know how to mimic the same in Power BI Modelling. I tried with One-Many/Many-Many everything works as same as INNER JOIN. 

 

Note: I can't use the Merge feature as I have many tables as shown below.

I can't use DAX, as I have so many columns (approx. 100) in each table, as I have so many columns with same name. 

So, please let me know how we can achieve LOJ in Modelling window itself

 

SELECT * FROM TABLE1

 

LEFT OUTER JOIN TABLE2
ON TABLE1.COLUMN1 = TABLE2.COLUMN1

 

LEFT OUTER JOIN TABLE3
ON TABLE1.COLUMN1 = TABLE3.COLUMN1

 

LEFT OUTER JOIN TABLE4
ON TABLE1.COLUMN1 = TABLE4.COLUMN1

 

LEFT OUTER JOIN TABLE5
ON TABLE1.COLUMN1 = TABLE5.COLUMN1

 

1 ACCEPTED SOLUTION

Hi @sivashankr ,

 

If there are blank values in visuals, they will be hided by default. It is by design for Power BI. So, after creating relationships, you need to turn on the option "Show items with no data". 

For you situation, you could turn it on and re-publish the reports to users. Then the report will working fine. Users don't need to turn on again by themselves.

 

 

change the cross filter direction from both to singlechange the cross filter direction from both to single

v-xuding-msft_0-1598853216187.png

 

Show items with no dataShow items with no data

 

 

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.

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

@sivashankr , not very clear.

But in power bi default is right join 1-M. If you add +0 to measure and view with 1 side of table it will become left join

 

measure = sum(TABLE2[value])

view by Table1[Col1]

 

others are

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Hi @amitchandak ,

Thanks much for the reply.

Here is my sample data set for 2 tables and actual output vs expected output.

I created Many-1 or 1-Many by interchanging the tables. But I still see the below output which is same as INNER JOIN.

I would like to see the highlighted records also in my output. Please note that I cant merge the tables because there so many similar tables. This should be achieved with Modelling tab only.

As per my understanding this can be achieved easily with Merge-->Left Outer Join. But I would like to know how to acieve the same in Modelling tab with cardinality

 

Untitled.png

 

Hi, it looks like inner by default in a visualization, but it's left/right you just need to allow the visualization show the blank or null results.

Try this: https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-show-items-no-data

Hope this helps,


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

Happy to help!

LaDataWeb Blog

Hello @ibarrau 

 

Thanks much for looking into this.

Originally, I created a dataset (model) and published to Service for user self-service reporting.

When I asked the users to turn-it on "Show items with no data", they questioned me "Can't we create a LOJ to pull all therecords from left table using Modelling window?"

 

So request you please let me know if there is anythng that we can do in Modeling window itself.?

Hi @sivashankr ,

 

If there are blank values in visuals, they will be hided by default. It is by design for Power BI. So, after creating relationships, you need to turn on the option "Show items with no data". 

For you situation, you could turn it on and re-publish the reports to users. Then the report will working fine. Users don't need to turn on again by themselves.

 

 

change the cross filter direction from both to singlechange the cross filter direction from both to single

v-xuding-msft_0-1598853216187.png

 

Show items with no dataShow items with no data

 

 

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.

THANK YOU! This solved a problem I had

Thanks @v-xuding-msft .

It worked as expected. Thank you.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.