How to show data from related table when there are no matches

[LuigiBros]

I have 2 tables (Table A and Table B) and relationship between them is 1:1.

Table A is Direct Query, Table B is import mode.

I have a problem with missing data in PBI report, basically PBI is not showing data when there is no matching data in Table B.

There is no problem in SQL query when I am joining them with LEFT JOIN.

CustomerID comes from Table A (direct query).

CustomerStatus, RegistrationDate, Payment Date come from Table B (import)

This is result I am getting in SQL and this is what I want to have in PBI:

CustomerID	CustomerStatus	RegistrationDate	PaymentDate
1113254	Registered	22/07/2024	23/07/2024
4654564	null	null	null
1156465	Blocked	21/07/2024	null
8979865	null	null	null

This is what I am currently getting in PBI:

CustomerID	CustomerStatus	RegistrationDate	PaymentDate
1113254	Registered	22/07/2024	23/07/2024
1156465	Blocked	21/07/2024	null

Ticking box "show items with no data" does not solve the issue.

[LuigiBros]

Generally it works, but data refresh on visualisations is veeeeeery slow.

I had this approach implemented before asking here as well.

And refresh times were too long to handle 😕

[Vertig00 · Accepted solution]

This is generally expected since the join happens "on the fly" due to the table being in DirectQuery.

Let's try to offload some resource consumption to Power BI then by using DAX ( it will still be slow but let's try if it's a bit faster). Since using a realtionship in the model directly will omit blanks, let's try using the RELATED function:

- Create a Calculated Table:

MergedTable =
ADDCOLUMNS(
TableA,
"CustomerStatus", RELATED(TableB[CustomerStatus]),
"RegistrationDate", RELATED(TableB[RegistrationDate]),
"PaymentDate", RELATED(TableB[PaymentDate])
)

- Create DAX Measures:

 --to handle the null values:

CustomerStatus = IF(ISBLANK(RELATED(TableB[CustomerStatus])), "No Data", RELATED(TableB[CustomerStatus]))

This way we should be able to simulate the SQL LEFT JOIN behavior and ensure that all data, including rows with missing values, are displayed correctly in your Power BI report.

[Vertig00]

Hello,

Since table A is in DirectQuery mode, creating a calculated table is out of the picture. In this case you should use Power Query to Handle the Join between the tables by merging the 2 queries(tables). This way, you can simulate the LEFT JOIN behavior. Here are the steps in a nutschell:

- Load Tables into Power Query - ensure both Table A and Table B are loaded into Power Query.
- Merge Queries

- select Table A in Power Query.
- select the "Join Kind" as "Left Outer (all from first, matching from second)".
- expand the merged table and select the columns you need

Now the data will be in 1 table similar to the result of the SQL SELECT statement.

Hope this helps. Let me know if that works .