How to compare two Rows value in same table and assign a value in a separate column based on it

[Anonymous]

Hi Team,

I need help in below logic:

I have three tables:
Orders table (Contains 1 row per orders placed with customer and revenue data as whole)

Order line items table (Contains brief Order data with line items bought within the order)

Product Stock Keeping Unit Table (It contains the Stock Keeping Unit and brand data)

Table data follows:

Order Table
Order No	Customer	Revenue
67894677	Customer A	580
67894600	Customer B	950
67890000	Customer C	800
67890012	Customer A	540

Order Line Items Table
Order No	Product SKU	Product SKU Code	Brand
67894677	Shampoo - 340 ml	13	Pantene
67894677	Hair Conditioner - 340 ml	11	Sunsilk
67894677	Hair Serum - 400ml	12	Matrix
67894600	Shampoo - 340 ml	14	Sunsilk
67894600	Hair Conditioner - 340 ml	11	Sunsilk
67894600	Hair Serum - 400ml	20	Sunsilk
67890000	Shampoo - 340 ml	13	Pantene
67890000	Hair Conditioner - 340 ml	10	Pantene
67890012	Hair Conditioner - 340 ml	15	Matrix
67890012	Hair Serum - 400ml	12	Matrix

Product Stock Keeping Unit
Product SKU	Product SKU Code	Brand
Hair Serum - 400ml	12	Matrix
Hair Conditioner - 340 ml	15	Matrix
Shampoo - 340 ml	13	Pantene
Hair Conditioner - 340 ml	10	Pantene
Hair Conditioner - 340 ml	11	Sunsilk
Shampoo - 340 ml	14	Sunsilk
Hair Serum - 400ml	20	Sunsilk

What help I need is that, I want to categories my orders on the basis of Brand Name

Expected, Assign brand name to orders and if there are more than 1 brand in one order mark it as "Mix Order". Please see the expected table below:

Order Table
Order No	Customer	Revenue	Calculated Brand Column
67894677	Customer A	580	Mix Brand
67894600	Customer B	950	Sunsilk Brand
67890000	Customer C	800	Pantene Brand
67890012	Customer A	540	Matrix Brand

Can someone please help me with the logic to create this calculated Column? I tried Compare function but it is not helping here.

[tamerj1 · Accepted solution]

@Anonymous 
Please use

Column = 
VAR Brand = CALCULATE ( SELECTEDVALUE ( 'Order Line Items'[Brand] ) )
RETURN
    IF ( 
        ISBLANK ( Brand ),
        "Mixed Order",
        Brand
    )

[Anonymous]

Thank you @tamerj1 , this solution worked.

[tamerj1]

Hi @Anonymous 
Suppose you have a one way relationhip (One to Many) between 'Orders' and 'Order Line Items' then you my try

Calculated Brand Column =
CONCATENATEX (
    RELATEDTABLE ( 'Order Line Items' ),
    IF (
        HASONEVALUE ( 'Order Line Items'[Brand] ),
        'Order Line Items'[Brand],
        "Mixed Order"
    )
)

[Anonymous]

Hi @tamerj1 

I tried the solution provided by you, but in my case it is not returning the expected value. When I am applying the DAX provide by you above, it is returning the values as in the screenshot below:

We need a little modification in the logic. 

[tamerj1]

Hi @Anonymous 
Please try

Calculated Brand Column =
VAR Check =
    SUMX (
        RELATEDTABLE ( 'Order Line Items' ),
        IF ( HASONEVALUE ( 'Order Line Items'[Brand] ), 1 )
    )
RETURN
    IF ( Check >= 1, VALUES ( 'Order Line Items'[Brand] ), "Mixed Order" )

[Anonymous]

Hi @tamerj1 ,

It is still only giving all values as "Mixed Order". Please see the screenshot below:

Can we compare orders column with Brand column, like if One Order is having multiple brand purchased together it returns "Mixed Order" else "Brand"?

[tamerj1]

@Anonymous 
Can you please create a column

COUNTROWS ( RELATEDTABLE ( 'Order Line Items' ) )

and let me know what results you get

[Anonymous]

Hi @tamerj1 

This is the result of above formula in 'Orders' Table.

[tamerj1]

Great @Anonymous 

Please use

Calculated Brand Column =
IF (
    COUNTROWS ( RELATEDTABLE ( 'Order Line Items' ) ) = 1,
    CALCULATE ( VALUES ( 'Order Line Items'[Brand] ) ),
    "Mixed Order"
)

[Anonymous]

Hi @tamerj1 ,

Thanks, above logic is working correctly to some extent but the result is still not accurate.

currently it is giving result as (if the order line item count is 1, then it is giving brand name) but in my case order can have multiple productsof brands in it but we need to see if the products in one order are of same Brand or not

For eg:

Scenario 1: 

Order Line Items Table
Order No	Product SKU	Product SKU Code	Brand
67890012	Hair Conditioner - 340 ml	15	Matrix
67890012	Hair Serum - 400ml	12	Matrix

Expected Brand Column result:

Order Table
Order No	Customer	Revenue	Calculated Brand Column
67890012	Customer A	540	Matrix Brand

Scenario 2:

Order Line Items Table
Order No	Product SKU	Product SKU Code	Brand
67894677	Shampoo - 340 ml	13	Pantene
67894677	Hair Conditioner - 340 ml	11	Sunsilk
67894677	Hair Serum - 400ml	12	Matrix

Expected Brand Column result:

Order Table
Order No	Customer	Revenue	Calculated Brand Column
67894677	Customer A	580	Mix Brand

This is what I am looking for to create. Please help me.