Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 Orde...
DAX
  • tamerj1's avatar
    tamerj1
    4 years ago

    Anonymous 
    Please use

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

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Great Anonymous 

Please use

 

 

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

 

 
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 NoProduct SKUProduct SKU CodeBrand
67890012Hair Conditioner - 340 ml15Matrix
67890012Hair Serum - 400ml12Matrix

 

Expected Brand Column result:

Order Table
Order NoCustomerRevenueCalculated Brand Column
67890012Customer A540Matrix Brand

 

Scenario 2:

Order Line Items Table
Order NoProduct SKUProduct SKU CodeBrand
67894677Shampoo - 340 ml13Pantene
67894677Hair Conditioner - 340 ml11Sunsilk
67894677Hair Serum - 400ml12Matrix

 

Expected Brand Column result:

Order Table
Order NoCustomerRevenueCalculated Brand Column
67894677Customer A580Mix Brand

 

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