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
Community Champion
4 years ago

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

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

        Anonymous 
        Can you please create a column

        COUNTROWS ( RELATEDTABLE ( 'Order Line Items' ) )

        and let me know what results you get