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

[Deevo_]

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

ProjectID	ProjectCode	ProjectName	ProjectCodeLevel1	ProjectCodeLevel2	ProjectCodeLevel3	ProjectCodeLevel4
1000	P/J-1234	Cloud Backup v1	P/J-1234	null	null	null
1001	P/J-1234-ICT	Cloud Backup v2	null	P/J-1234-ICT	null	null
1002	P/J-1234-ICT-01	Cloud Backup v3	null	null	P/J-1234-ICT-01	null
1003	P/J-1234-ICT-01-001	Cloud Backup v4	null	null	null	P/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)

SuppliedProjectCode	SuppliedProjectName	SuppliedProjectCodeLevel1	SuppliedProjectCodeLevel2	SuppliedProjectCodeLevel3	SuppliedProjectCodeLevel4
P/J-1234-ICT-01	Cloud Backup v3	null	null	P/J-1234-ICT-01	null

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.

• ProjectID	ProjectCode	ProjectName	ProjectCodeLevel1	ProjectCodeLevel2	ProjectCodeLevel3	ProjectCodeLevel4
  1002	P/J-1234-ICT-01	Cloud Backup v3	null	null	P/J-1234-ICT-01	null

Many thanks

[Deevo_]

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

[technolog]

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.