Forum Discussion
How to compare two Rows value in same table and assign a value in a separate column based on it
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.
Anonymous
Please useColumn = VAR Brand = CALCULATE ( SELECTEDVALUE ( 'Order Line Items'[Brand] ) ) RETURN IF ( ISBLANK ( Brand ), "Mixed Order", Brand )
10 Replies
Anonymous
Please useColumn = VAR Brand = CALCULATE ( SELECTEDVALUE ( 'Order Line Items'[Brand] ) ) RETURN IF ( ISBLANK ( Brand ), "Mixed Order", Brand )Thank you tamerj1 , this solution worked.
Hi Anonymous
Suppose you have a one way relationhip (One to Many) between 'Orders' and 'Order Line Items' then you my tryCalculated Brand Column = CONCATENATEX ( RELATEDTABLE ( 'Order Line Items' ), IF ( HASONEVALUE ( 'Order Line Items'[Brand] ), 'Order Line Items'[Brand], "Mixed Order" ) )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.
Hi Anonymous
Please tryCalculated 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" )