Forum Discussion

ahmedshalabyy12's avatar
ahmedshalabyy12
Helper V
1 year ago
Solved

Relationships

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 ?

Need Help
  • Shivu-2000's avatar
    Shivu-2000
    1 year ago

    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!

3 Replies

  • Shivu-2000's avatar
    Shivu-2000
    Responsive Resident
    1 year ago

    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!

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Community Support
    1 year ago

    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.