Relationships

[ahmedshalabyy12]

Hello,

i have 2 columns comes from the client for example ID1  and ID2 ( Fact Table ) 

i make relationship with dim_emp between (Dim_ID1) and (Fact_ID1 )

the relation workins fine for most of the agents

but i have one problem that some agents that comes from the client don't have ID1 but have ID2

so i want to tell power bi if you didn't find ID1 so make the relation with ID2

how i can make that ?

[v-saisrao-msft]

Hi @ahmedshalabyy12,

I hope you had a chance to review the solution shared by @Shivu-2000 . If it addressed your question, please consider accepting it as the solution — it helps others find answers more quickly.
If you're still facing the issue, feel free to reply, and we’ll be happy to assist further.

Thank you.

[Shivu-2000 · Accepted solution]

Hi  @ahmedshalabyy12 

As per my understanding your data is as: 

You have a Fact table that contains two ID columns from the client:

• ID1

• ID2

  You also have a Dimension table (Dim_Emp) with the column:

  • Dim_ID1

    Currently, 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] and Dim_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!

[ahmedshalabyy12]

Thank you it worked apperciate your effort