Table visualization left outer join without creating a custom query

[Anonymous]

I'm creating a table visualization based on two tables in my data model; site (containing location information) and entity_phone (containing phone numbers, but not only for sites but also for persons etc.). In this table I'd like to show a site number with a corresponding phone number. The table however shows many rows with only information from the entity_phone table (i.e. persons etc), without a corresponding site number. 

Is there a way to set up the table visualization behaving like a left outer join without merging two queries in the Power Query editor? Since I have to set up many tables in this report I'd like to minimize the amount of merges in my data model.

I've tried to set up a visualization filter in which I filter out the empy site numbers which I've locked and hid for end users. This works but every time I switch to another visualization (through bookmarks) the filter is automatically reset. This might be a whole new issue however.

[v-yingjl · Accepted solution]

Hi @Anonymous ,

1. Based on your description, if you don't want to show the rows which site_no column is not blank, you can set a filter on this page like this:

Don't forget to right click the bookmark to update it after setting filters so that the bookmark status will not reset.

2. Whenever we create a relationship, Power BI implment INNER JOIN by default. If you want to get a left outer join, you can try to use NATURALLEFTOUTERJOIN() function. Refer this issue:  left outer join with out creating new query or table. is any DAX query  

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.

[Anonymous]

Updating the bookmark was where I went wrong, just recently started using Power BI, thank you 🙂

[amitchandak]

@Anonymous , In visual if we create a measure with +0 then it will create a left join with dimensions(1 in 1-to Many)

Measure = sum(Table[Sales]) +0

[HotChilli]

Too many words.

Can you give us sample data for the two tables - and tell us if there is a relationship between them .

Also show us the desired output.

Someone will answer you.

[Anonymous]

Sure, these are the values in the table:

With site information coming from the site table and phone_number coming from the entity_phone table (as well as some other fields coming from other tables).

And this is how it currently appears:

Many phone numbers have no associated site, but still appear in the table, which I would like to get rid off.

Because the primary key in the table is not unique (in the SQL database it is part of a compound key) I've created a compound key in Power BI which I use to link the two tables:

If you need additional information let me know. Thank you for your help 🙂