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
This question was redirected from
Using 2 IDs to create conditional relationship b/w 2 tables
Link Fact and Dim using 2 IDs based on a conditional logic
Another variation of this scenario is the case wherein there are multiple dimension tables being reference. I have been trying to find solution for such but I haven't found any. Can somebody please help? Relating to this example, If F_ID is null, I_ID is used but I_ID is a unique key from a different dimension table.
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.
AS I have commented on original question, would you happen to have a solution for my scenario?
7 people are following this question.
