Forum Discussion
Relationships
As per my understanding your data is as:
You have a Fact table that contains two ID columns from the client:
-
ID1 -
ID2You also have a Dimension table (
Dim_Emp) with the column:-
Dim_ID1Currently, you've created a relationship between:
-
Fact[ID1] →Dim_Emp[Dim_ID1]This works fine for most agents.
However, for some records,ID1 is blank or missing, butID2 has a valid value.
You want Power BI to fall back to useID2 whenID1 is not available for establishing the relationship.
My solution to this will be create anewCalculated column the Fact table that acts as a fallback ID by checkingID1 first, and usingID2 ifID1 is blank.Step 1: Add a calculated column
In Power BI, go to your Fact table and create a new column using the below DAX:
Effective_ID = IF ( NOT ISBLANK(Fact[ID1]), Fact[ID1], Fact[ID2] )Step 2: Update the relationship
In Model view:
-
Delete the existing relationship between
Fact[ID1] andDim_Emp[Dim_ID1] -
Create a new relationship between:
Fact[Effective_ID] →Dim_Emp[Dim_ID1]This ensures Power BI uses
ID1 when it’s present, and falls back toID2 otherwise.If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Happy to help!-
As per my understanding your data is as:
You have a Fact table that contains two ID columns from the client:
-
ID1 -
ID2You also have a Dimension table (
Dim_Emp) with the column:-
Dim_ID1Currently, you've created a relationship between:
-
Fact[ID1] → Dim_Emp[Dim_ID1]
This works fine for most agents.
However, for some records, ID1 is blank or missing, but ID2 has a valid value.
You want Power BI to fall back to use ID2 when ID1 is not available for establishing the relationship.
My solution to this will be create a new Calculated column the Fact table that acts as a fallback ID by checking ID1 first, and using ID2 if ID1 is blank.
Step 1: Add a calculated column
In Power BI, go to your Fact table and create a new column using the below DAX:
Effective_ID = IF ( NOT ISBLANK(Fact[ID1]), Fact[ID1], Fact[ID2] )
Step 2: Update the relationship
In Model view:
-
Delete the existing relationship between
Fact[ID1] andDim_Emp[Dim_ID1] -
Create a new relationship between:
Fact[Effective_ID] → Dim_Emp[Dim_ID1]
This ensures Power BI uses ID1 when it’s present, and falls back to ID2 otherwise.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Happy to help!
Thank you it worked apperciate your effort