Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

conditional relationship

Hi, I am importing tables from SQL Server into Power Bi. Two tables, named TB and AP, have a 1-to-many relationship with a condition, ie:

TB.VLAS=AP.TPGE AND
CDA = '025' AND
NMF = 'AP' and NMC = 'TPGE'

What should I do to create this conditional relationship between the two tables? Can you please explain what to do? I am new to Power Bi, thank you!

Need Help

4 Replies

  • fahadqadir3's avatar
    fahadqadir3
    Solution Supplier
    2 years ago

    Anonymous  Add calculated columns in both TB and AP tables to capture the condition for the relationship.

    In the TB table, create a calculated column:

    ConditionalKey_TB = IF(TB[CDA] = "025" && TB[NMF] = "AP" && TB[NMC] = "TPGE", TB[VLAS], BLANK())

     In "AP" Table 

    ConditionalKey_AP = IF(AP[CDA] = "025" && AP[NMF] = "AP" && AP[NMC] = "TPGE", AP[TPGE], BLANK())

    Once you have created these calculated columns, you can establish a relationship between the TB and AP tables based on these new columns.

    Did I answer your question? Then please mark my post as the solution.
    If I helped you, click on the Thumbs Up to give Kudos.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      i don't have a column in TB named CDA. I have this column only on AP and column NMC is only on TB table, not in AP table. So how i can edit your query? thank you

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        please, someone can help me?

  • HotChilli's avatar
    HotChilli
    Community Champion
    2 years ago

    General advice: Most tables/relationships in SQL Server are designed as a balance between input/update/selection.  They are supposed to be normalised (supposed to be). Powerbi is a reporting system which is designed for reading of data.  Use a star schema to optimise this and you transform the data in Power Query.

    --

    So the answer to your question may go away because you've changed the design. It is,generally, a mistake to import the tables from SQL Server as they are and try to recreate the relationships. In powerbi, you can't have multiple key relationships anyway.