Conditional Column based on Matching Column in another Table

[mpribadi07]

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
1	Baby
2	Tena
3	Allergy
4	First Aid

Table 2. 

WIC	Ship To
1	DC 01
2	DC 02
3	DC 03
4	DC 04

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

WIC	Ship To	Result
1	DC 01	Baby
2	DC 02	Tena
3	DC 03	Allergy
4	DC 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

[v-alq-msft · Accepted solution]

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.

[Ashish_Mathur]

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.

[PhilipTreacy]

 @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

[PhilipTreacy]

Hi @mpribadi07 

Download example PBIX file

Assuming the tables have a (1-1) relationship, this code creates a column in Table2

ToResult = IF('Table2'[Ship To] = "DC 04" , "DOT COM", RELATED(Table1[Opstudy]))

regards

Phil

[mpribadi07]

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