Replacing blank values on join using relationship

[shane7mcdonald]

Hi all, 

I have a simple model with a DIM and FACT (1-M) table and I'm basically using the relationship between the two to do a left outer join, which works fine (see below).

DIM table

Qual ID	Qual Name
1	Qual A
2	Qual B
3	Qual C
4	Qual D
5	Qual E

FACT table

Qual ID	Name	Expiry Date
2	Joe Smith	12/7/24
3	Joe Smith	17/12/27
4	Joe Smith	7/1/26

The result looks like this (which is what I need)

Table visual

Qual Name (DIM table)	Name (Fact table)	Expiry Date (Fact table)
Qual B	Joe Smith	12/7/24
Qual C	Joe Smith	17/12/27
Qual D	Joe Smith	7/1/26
Qual A
Qual E

But I'd like it to look like this for the blank values...

Qual Name (DIM table)	Name (Fact table)	Expiry Date (Fact table)
Qual B	Joe Smith	12/7/24
Qual C	Joe Smith	17/12/27
Qual D	Joe Smith	7/1/26
Qual A	Joe Smith	Not Complete
Qual E	Joe Smith	Not Complete

I'm a bit stumped on where to go next.

Note: Both these tables are large (DIM = 5,000 rows, FACT = 2,000,000 rows) so anything in power query is a no go.

Thanks eveyone, hopefully there are a few ideas out there 🤞

[AmiraBedh]

In your dimension you need :

a calculated column for the name :

Adjusted Name = 
VAR currentQualID = 'DIM Table'[Qual ID]
VAR nameFromFact = CALCULATE(
    MAX('FACT Table'[Name]),
    'FACT Table'[Qual ID] = currentQualID
)
RETURN
IF(
    ISBLANK(nameFromFact),
    "Joe Smith",
    nameFromFact
)

Then to avoid circular dependency I created a measure :

Adjusted Expiry Date  = 
VAR currentQualID = MAX('DIM Table'[Qual ID])
VAR expiryDateFromFact = CALCULATE(
    MAX('FACT Table'[Expiry Date]),
    'FACT Table'[Qual ID] = currentQualID
)
RETURN
IF(
    ISBLANK(expiryDateFromFact),
    "Not Complete",
    expiryDateFromFact
)

I can provide another solution if you can use a calculated table.

[shane7mcdonald]

Hi @AmiraBedh, thanks for the reply. I should have mentioned a couple of things, the example I gave above was for a single person only, but its probably a bit more complicated.

1. The Qualification DIM table contains 5000 unique qualification names
2. The Employee Qualifications FACT table contains thousands of people who have hundreds of qualifications each

My problem statement is this: I need to find out for each person (20,000 people) which qualification they have and dont have against the list of 5000 unique qualification names. e.g. Joe Smith might have 100 qualifications and be missing 4900, Mary Jane might have 10 qualifications and be missing 4990.

Obviously there some filter context also to reduce the data in the output table. The user will filter by 2 slicers.

1. The first one is a Qualification slicer (joining on Qualification ID)
2. The second is a Organisation Chart slicer (joining on Org Number ID)

So the user will filter by the team they belong to AND the qualification they are searching for.

Example:

User searches for Team A in the Org DIM slicer and

User searches for Qual B in the Qualification DIM slicer

The output should be like below (obviously it returns the rows where someone DOES have the qualification but I'm also chasing the rows where people DOES NOT have the qualification - in bold)

Qual Name	Name	Expiry Date
Qual B	Joe Smith	27/3/28
Qual B	Mary Jane	1/5/27
Qual B	Peter Stanley	Not Complete
Qual B	Kate Davis	Not Complete
Qual B	Kim Carter	Not Complete
Qual B	Eric Hughes	Not Complete

I would send through a sample file but it is business sensitive data. Sorry!