Forum Discussion

mpribadi07's avatar
mpribadi07
Regular Visitor
5 years ago
Solved

Conditional Column based on Matching Column in another Table

Hi all,

 

I would like to gain some of your Power BI wisdom to solve my problem below.

 

So I have 2 tables as follow:

Table 1.

WIC Opstudy
1Baby
2Tena
3Allergy
4First Aid

 

Table 2. 

WICShip To
1DC 01
2DC 02 
3DC 03 
4DC 04 

 

I would like to add a conditional column on Table 2 with result as follow:

WICShip ToResult
1DC 01Baby
2DC 02 Tena
3DC 03 Allergy
4DC 04 DOT COM

 

The logic is:

If "Ship To" = DC 04, print "DOT COM"

Else -> Return the corresponding "Opstudy" value from Table 1 based on the matching WIC.

 

However, the 2 tables have a many to many relationship.

 

Is this possible through a power query or DAX calculation?

Thank you so much!

 

Best Regards,

Marsha Pribadi

Need Help
Tips and Tricks
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, mpribadi07 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table1:

     

    Table2:

     

    Relationship(Many to Many):

     

    You may create a calculated column as below.

    Result Column = 
var wic = [WIC]
return
IF(
        [Ship To]="DC 04",
        "DOT COM",
        CONCATENATEX(
            FILTER(
               Table1,
               [WIC]=wic
            ),
            [Opstudy],
            ","
        )
)

     

    Result:

     

    Or you may try the following calculated table.

    Table = 
ADDCOLUMNS(
    SUMMARIZE(
        Table2,
        Table2[WIC],
        "Ship To",
        CONCATENATEX(
            Table2,
            [Ship To],
            ","
        )
    ),
    "Result",
    var wic = [WIC]
    return
    IF(
        [Ship To]="DC 04",
        "DOT COM",
        CONCATENATEX(
            FILTER(
               Table1,
               [WIC]=wic
            ),
            [Opstudy],
            ","
        )
    )
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

    • mpribadi07's avatar
      mpribadi07
      Regular Visitor
      5 years ago

      Hi Phil,

       

      Thank you so much for your reply!

       

      The Tables have a many to many relationship.

       

      I will try the code you provided but appreciate any insights if you think the code won't work for a many to many relationship. 

       

      Thank you!

       

      Marsha 

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

     mpribadi07 

     

    Hi Marsha,

     

    Please @ mention me or I'll miss your replies.  Type @ then select my name.

     

    The data you provided would have a 1-1 relationship.  You should avoid M-M.  My code won't work for M-M. 

    Can you change your tables so that they have 1-1 or 1-M?

    Looking at Table2 I'd expect that to have unique entries for WIC as I'd expect you can only ship to a single DC?

     

    Your stated logic is 

     

    If "Ship To" = DC 04, print "DOT COM"

    Else -> Return the corresponding "Opstudy" value from Table 1 based on the matching WIC.

     

    But if a WIC value appears multiple times in Table1 there is no corresponding Opstudy value.  There are multiple matching values. How to know which one to use?

     

    The values in WIC column of Table 1 need to unique in that column.

     

    Regards

    Phil

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    Please show relevant data in both tables (with entries being repeated in the WIC columns of both tables) and on that data show the expected result.

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, mpribadi07 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table1:

     

    Table2:

     

    Relationship(Many to Many):

     

    You may create a calculated column as below.

    Result Column = 
var wic = [WIC]
return
IF(
        [Ship To]="DC 04",
        "DOT COM",
        CONCATENATEX(
            FILTER(
               Table1,
               [WIC]=wic
            ),
            [Opstudy],
            ","
        )
)

     

    Result:

     

    Or you may try the following calculated table.

    Table = 
ADDCOLUMNS(
    SUMMARIZE(
        Table2,
        Table2[WIC],
        "Ship To",
        CONCATENATEX(
            Table2,
            [Ship To],
            ","
        )
    ),
    "Result",
    var wic = [WIC]
    return
    IF(
        [Ship To]="DC 04",
        "DOT COM",
        CONCATENATEX(
            FILTER(
               Table1,
               [WIC]=wic
            ),
            [Opstudy],
            ","
        )
    )
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.