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

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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous 
    Can you please create a column

    COUNTROWS ( RELATEDTABLE ( 'Order Line Items' ) )

    and let me know what results you get

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi tamerj1 

       

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

       

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