Forum Discussion
Replacing blank values on join using relationship
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!