Link Fact and Dim using 2 IDs based on a conditional logic

Question

Objective: Use 2 IDs to create conditional relationship b/w fact and dim table

The conditional logic should be:

• Use F_ID if exists.
• Also if F_ID is NULL then use I_ID to link records

Answer 1

This can be achieved by adding a calculated column to both tables that will create a composite key based on whether or not F_ID is null or not. Where this would not necessarily work if Rec_No 5 in your dimension table had a value in F_ID, so you may need to handle that case.

The DAX for the composite key is:
Comp_Key = IF(ISBLANK([F_ID]) || [F_ID] == "NULL", [I_ID], [F_ID])

And the resulting table would look something like this:

By adding that column to both tables you'll be able to create the relationship you want.