Forum Discussion

shane7mcdonald's avatar
shane7mcdonald
Frequent Visitor
2 years ago

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 Qu...
Need Help
AmiraBedh's avatar
AmiraBedh
Icon for Super User rankSuper User
2 years ago

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.