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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Deevo_
Helper II
Helper II

Create a relationship to link 2 tables by using a hierarchy linking rule

Hi everyone,

This seems complicated, so I will explain as best as I can using easy to digest sample dummy data.

 

What I want to achieve:

  • I want to link two tables together by using a ranking condition. I am not sure how to go about this.

Datasets:

SQL Project Table

ProjectIDProjectCodeProjectNameProjectCodeLevel1ProjectCodeLevel2ProjectCodeLevel3ProjectCodeLevel4
1000P/J-1234Cloud Backup v1 P/J-1234nullnullnull
1001P/J-1234-ICTCloud Backup v2nullP/J-1234-ICTnullnull
1002P/J-1234-ICT-01Cloud Backup v3nullnullP/J-1234-ICT-01null
1003P/J-1234-ICT-01-001Cloud Backup v4nullnullnullP/J-1234-ICT-01-001

 

Excel Project Table: (This table is supplied from an external source and is used to match the Project codes in the above table)

SuppliedProjectCodeSuppliedProjectNameSuppliedProjectCodeLevel1SuppliedProjectCodeLevel2SuppliedProjectCodeLevel3SuppliedProjectCodeLevel4
P/J-1234-ICT-01Cloud Backup v3nullnullP/J-1234-ICT-01null

 

Requirements:

  • Using the supplied "Excel Project Table", I need to perform a matching check to the "SQL Project Table" to see if the Project code exists.
  • I need to match at the lowest level first, then if there is no match on that level, check the next level until we get to the highest level. So in this order: Level 4, Level 3, Level 2 and then finally level 1.
    • Example:
      • IF (

'SQLProject Table'[ProjectCodeLevel4] = 'Excel Project Table'[ProjectCodeLevel4] && 'SQLProject Table'[ProjectCodeLevel4] <> BLANK(), 'SQLProject Table'[ProjectCodeLevel4], 

IF ('SQLProject Table'[ProjectCodeLevel3] = 'Excel Project Table'[ProjectCodeLevel3] && 'SQLProject Table'[ProjectCodeLevel3] <> BLANK(), 'SQLProject Table'[ProjectCodeLevel3], 

IF ('SQLProject Table'[ProjectCodeLevel2] = 'Excel Project Table'[ProjectCodeLevel3] && 'SQLProject Table'[ProjectCodeLevel2] <> BLANK(), 'SQLProject Table'[ProjectCodeLevel2], 

IF ('SQLProject Table'[ProjectCodeLevel1] = 'Excel Project Table'[ProjectCodeLevel1] && 'SQLProject Table'[ProjectCodeLevel1] <> BLANK(), 'SQLProject Table'[ProjectCodeLevel1],

BLANK()

)

 

Expected results:

  • Always checks the level 4 codes first, but if there is no match. Then checks the level 3 code and finds a match. The report should display the level 3 code based on our matching conditions.
  • ProjectIDProjectCodeProjectNameProjectCodeLevel1ProjectCodeLevel2ProjectCodeLevel3ProjectCodeLevel4

    1002

    P/J-1234-ICT-01Cloud Backup v3nullnullP/J-1234-ICT-01null

     

 

Many thanks

2 REPLIES 2
Deevo_
Helper II
Helper II

Thank you for your time!!! Very nifty solution. I will test it out and let you know how i go!

technolog
Super User
Super User

Alright, I get what you're trying to achieve. You want to match the project codes from the Excel Project Table to the SQL Project Table, starting from the lowest level and moving up. If there's a match at any level, you want to stop and display that match.

The DAX formula you provided is on the right track. It's using nested IF statements to check each level, starting from Level 4 and moving up to Level 1. If there's no match at any level, it returns BLANK().

However, the way you've set it up, it's more of a calculated column approach. If you want to create a relationship between the two tables, you'll need a common key. But, given the nature of your data, it's a bit tricky to create a direct relationship.

One approach is to create a calculated column in both tables that uses the logic you provided to determine the matching project code. Once you have this calculated column in both tables, you can then create a relationship between them using this new column.

Here's how you can do it:

Create a new calculated column in the SQL Project Table:
MatchingCode =
IF (
NOT(ISBLANK('SQLProject Table'[ProjectCodeLevel4])),
'SQLProject Table'[ProjectCodeLevel4],
IF (
NOT(ISBLANK('SQLProject Table'[ProjectCodeLevel3])),
'SQLProject Table'[ProjectCodeLevel3],
IF (
NOT(ISBLANK('SQLProject Table'[ProjectCodeLevel2])),
'SQLProject Table'[ProjectCodeLevel2],
IF (
NOT(ISBLANK('SQLProject Table'[ProjectCodeLevel1])),
'SQLProject Table'[ProjectCodeLevel1],
BLANK()
)
)
)
)
Create a similar calculated column in the Excel Project Table:
MatchingCode =
IF (
NOT(ISBLANK('Excel Project Table'[SuppliedProjectCodeLevel4])),
'Excel Project Table'[SuppliedProjectCodeLevel4],
IF (
NOT(ISBLANK('Excel Project Table'[SuppliedProjectCodeLevel3])),
'Excel Project Table'[SuppliedProjectCodeLevel3],
IF (
NOT(ISBLANK('Excel Project Table'[SuppliedProjectCodeLevel2])),
'Excel Project Table'[SuppliedProjectCodeLevel2],
IF (
NOT(ISBLANK('Excel Project Table'[SuppliedProjectCodeLevel1])),
'Excel Project Table'[SuppliedProjectCodeLevel1],
BLANK()
)
)
)
)
Now, you can create a relationship between the 'SQLProject Table' and the 'Excel Project Table' using the 'MatchingCode' column you just created in both tables.
Once you've set up this relationship, you can use it to create visuals, tables, or other reports that combine data from both tables based on the matching project codes.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors