Forum Discussion

HPS1RT's avatar
HPS1RT
Frequent Visitor
2 years ago
Solved

Power BI Relationship

Hello Community,

I have 2 tables and wanted to build the relationship between these tables as below shown. Could you please help me how to build relationship between them?

Table A:
Issue-1   Issue-2

AD
BE
CF


Table B:
Issue

A
B
C
D
E
F


I don't want to create a duplicate table A and append Issue 1 and Issue 2 or even Unpivot, because it will create duplicate rows

Need Help
  • SamWiseOwl's avatar
    SamWiseOwl
    2 years ago

    Hi HPS1RT 

    There are several ways to approach this:

    1. Use the Unpivot option in the Query Editor
    2. Create two copies of the Table A called Issue 1 and Issue 2
    3. Make measures using the UseRealtionship() function

     

    First option in the Query Editor choose the issue columns and Unpivot:

    Second Option duplicate the tables:

    Then join the two tables once with Issue 1 and once with Issue 2:

    The third option is to join the same table twice:

    Only one relationship can be active at a time, so you need measures to change the active relationship.

    All measures using Issue 1 will work as normal, Issue 2 will require each to be a seperate measure.

    For example:

    Count Issue 1 = Count( 'Table'[Issue])

    Count Issue 2 = Calculate( Count( 'Table'[Issue]), UseRelationship('Table B'[Issue], 'Table A Issue 2'[Issue-2]))
     
    If this helps please mark as a solution for others to find.

2 Replies

  • SamWiseOwl's avatar
    SamWiseOwl
    Super User
    2 years ago

    Hi HPS1RT 

    There are several ways to approach this:

    1. Use the Unpivot option in the Query Editor
    2. Create two copies of the Table A called Issue 1 and Issue 2
    3. Make measures using the UseRealtionship() function

     

    First option in the Query Editor choose the issue columns and Unpivot:

    Second Option duplicate the tables:

    Then join the two tables once with Issue 1 and once with Issue 2:

    The third option is to join the same table twice:

    Only one relationship can be active at a time, so you need measures to change the active relationship.

    All measures using Issue 1 will work as normal, Issue 2 will require each to be a seperate measure.

    For example:

    Count Issue 1 = Count( 'Table'[Issue])

    Count Issue 2 = Calculate( Count( 'Table'[Issue]), UseRelationship('Table B'[Issue], 'Table A Issue 2'[Issue-2]))
     
    If this helps please mark as a solution for others to find.