topic Re: How to compare two Rows value in same table and assign a value in a separate column based on it in DAX Commands and Tips

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

Anonymous — Wed, 11 May 2022 13:35:47 GMT

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.

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

tamerj1 — Wed, 11 May 2022 14:36:05 GMT

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" ) )

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

Anonymous — Thu, 12 May 2022 19:40:51 GMT

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.

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

tamerj1 — Fri, 13 May 2022 09:01:10 GMT

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" )

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

Anonymous — Fri, 13 May 2022 10:14:49 GMT

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"?

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

tamerj1 — Fri, 13 May 2022 10:30:44 GMT

Anonymous
Can you please create a column

COUNTROWS ( RELATEDTABLE ( 'Order Line Items' ) )

and let me know what results you get

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

Anonymous — Fri, 13 May 2022 10:53:50 GMT

Hi tamerj1

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

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

tamerj1 — Fri, 13 May 2022 12:17:45 GMT

Great Anonymous

Please use

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

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

Anonymous — Fri, 13 May 2022 13:54:54 GMT

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.

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

tamerj1 — Fri, 13 May 2022 15:10:56 GMT

Anonymous
Please use

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

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

Anonymous — Mon, 16 May 2022 04:23:05 GMT

Thank you tamerj1 , this solution worked.