Show rows without relationship in PowerBI visual

[NickDekker]

I have two table. One main table which is a direct query and a subtable which is an Excel-file. The two table are related to each other through two columns. However, there is not always a relationship between the two columns since the values do not match. For example, in the main table the value is C66709999 and in the subtable I could be C667099. 

When there is no relationship I would like to keep showing the rest of the record and leave the columnvalue which is added through the relationship empty. I have tried several of thing but since I am dealing with a direct query, I am not able to edit the joins in the Power Query or to make a calcuated column. 

[v-sdhruv]

Hi @NickDekker ,

Just wanted to check if you were able to resolve the issue?
If you are still facing any challenges, consider checking this out too-

Show Data from table that has no relationship to another table

Thank you @sanalytics   and @lbendlin   for sharing your inputs.

Hope this helps!

[sanalytics · Accepted solution]

@NickDekker 
If your datasets is too much large then using Show items with No data is not recomended. Also, creating Calculated column can hamper your performance.. 
what you can do, you can create a disconnected table and use that table for showing non relationship value. For example below screenshot

I have attached my testing pbix file. Hope it will help you.

May i know why you are using Directquery??

Regards

sanalytics

[lbendlin]

Consider using "Show items with no data".

Consider disconnecting the tables and use measures.

Be aware that you can add calculated columns to Direct Query sources under specific circumstances.

[NickDekker]

The dataset is too large to use Show Items with no data. When I use that option, the visual and page filter don't work anymore.