Forum Discussion
Replacing blank values on join using relationship
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 🤞
2 Replies
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.
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.
- The Qualification DIM table contains 5000 unique qualification names
- 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.
- The first one is a Qualification slicer (joining on Qualification ID)
- 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!
