question

AbhaySudhakaran-7972 avatar image
0 Votes"
AbhaySudhakaran-7972 asked ·

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

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

79849-image.png


azure-analysis-services
image.png (11.8 KiB)
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

SamaraSoucy-MSFT avatar image
0 Votes"
SamaraSoucy-MSFT answered ·

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:

80346-2021-03-22-19-29-29-window.png

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



·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.