How to return different record or value when JOIN relationship returns empty in Power BI?

Question

Is it possible to select different (or set default) record if JOIN relationship returns empty in Power BI?

For examples, I have the following three tables:

Date Table:

Date
2023/01
2023/02
2023/03

Client Table:

ClientID	ClientDate
C1	2023/01
C2	2023/02
C3	2023/03

SalesRep Table:

SRID	ClientID
S1	C1
S2	C2

If I have a Date slicer and set to 2023/03, the ClientID/SalesRep table visual will have C3 but empty SalesRep. I would like that empty value to default to ClientID, which is C3.

I can accomplish this in SQL by testing the NULL for SRID column after tables are joined. But need to do this in Power BI because data comes from multiple sources.

Any help is much appreciated.

Answer 1

I assume that you have the following relationships between your tables:

• Client Table and Date Table using ClientDate and Date fields.
• Client Table and SalesRep Table using ClientID fields.

Then in Power Query, create a new query by merging Client Table with SalesRep Table on the ClientID field, if SRID is null for any row, fill it with the value from ClientID (This can be done using the "Replace Values") Or using DAX, create a new table a:

NewTable =
ADDCOLUMNS(
    'Client Table',
    "SalesRep", 
    COALESCE(
        RELATED('SalesRep Table'[SRID]),
        'Client Table'[ClientID]
    )
)