question

AbhaySudhakaran-7972 avatar image
0 Votes"
AbhaySudhakaran-7972 asked MrazAnaMinela-8841 commented

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)
· 1
5 |1600 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.

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.

0 Votes 0 ·

1 Answer

SamaraSoucy-MSFT avatar image
0 Votes"
SamaraSoucy-MSFT answered MrazAnaMinela-8841 commented

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.



· 1
5 |1600 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.

AS I have commented on original question, would you happen to have a solution for my scenario?

0 Votes 0 ·