Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
shane7mcdonald
Frequent Visitor

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 IDQual Name
1Qual A
2Qual B
3Qual C
4Qual D
5Qual E

 

FACT table

Qual IDNameExpiry Date
2Joe Smith12/7/24
3Joe Smith17/12/27
4Joe Smith7/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 BJoe Smith12/7/24
Qual CJoe Smith17/12/27
Qual DJoe Smith7/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 BJoe Smith12/7/24
Qual CJoe Smith17/12/27
Qual DJoe Smith7/1/26
Qual AJoe SmithNot Complete
Qual EJoe SmithNot 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 2
AmiraBedh
Resident Rockstar
Resident Rockstar

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
)

 

AmiraBedh_0-1718106636229.png

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


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

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

shane7mcdonald_1-1718111666449.png

 

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 NameNameExpiry Date
Qual BJoe Smith27/3/28
Qual BMary Jane1/5/27
Qual BPeter StanleyNot Complete
Qual BKate DavisNot Complete
Qual BKim CarterNot Complete
Qual BEric HughesNot Complete

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.