Forum Discussion

akp's avatar
akp
Frequent Visitor
8 months ago
Solved

Lookup for a value and compare DAX

Hi all,

I have a sample table like below with multiple rows for each Order.
I am trying to check  for an Order with specific discount category value of ABC, for the same Order if there is a row entry for DEF with Discount Eligibility = TRUE then the "calculated eligibility" should be "Yes" for the row with discount category value of ABC.

OrderDatediscount categoryDiscount Eligibilitycalculated eligibility
100000210/1/2025DEFTRUEn/a
100000210/1/2025ABCFALSEYes

 

Appreciate some direction here. EARLIER function and COUNTROWS was resulting incorrect results so far.

Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    8 months ago

    Use a calculated column that checks “does this Order have DEF = TRUE?”, and only returns Yes on the ABC row.

    Calculated Eligibility =
VAR ThisOrder = 'Table'[Order]
VAR HasDefTrue =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Order] = ThisOrder
                && 'Table'[discount category] = "DEF"
                && 'Table'[Discount Eligibility] = TRUE ()
        )
    ) > 0
RETURN
IF (
    'Table'[discount category] = "ABC" && HasDefTrue,
    "Yes",
    "n/a"
)

4 Replies

  • cengizhanarslan's avatar
    cengizhanarslan
    Icon for Super User rankSuper User
    8 months ago

    Use a calculated column that checks “does this Order have DEF = TRUE?”, and only returns Yes on the ABC row.

    Calculated Eligibility =
VAR ThisOrder = 'Table'[Order]
VAR HasDefTrue =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Order] = ThisOrder
                && 'Table'[discount category] = "DEF"
                && 'Table'[Discount Eligibility] = TRUE ()
        )
    ) > 0
RETURN
IF (
    'Table'[discount category] = "ABC" && HasDefTrue,
    "Yes",
    "n/a"
)
  • alish_b's avatar
    alish_b
    Icon for Super User rankSuper User
    8 months ago

    Hey akp ,

     

    Try the following calculated field:

    calculated eligibility = 
IF(
    [discount category] = "ABC",
    VAR OrderID = [Order]
    VAR HasDEF = 
        COUNTROWS(
            FILTER(
                'Table',
                'Table'[Order] = OrderID &&
                'Table'[discount category] = "DEF" &&
                'Table'[Discount Eligibility] = TRUE()
            )
        ) > 0
    RETURN IF(HasDEF, "Yes", "No"),
    "n/a"
)

    Replace the references where necessary. Basically, trying a EXISTS pattern, so even if one instance of the DEF and TRUE pair comes for the OrderId, it will update that order's ABC record.

    Hope it helps!

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    8 months ago

    akp 

    you can try this

     

    Column =
    var _t=maxx(FILTER('Table','Table'[Order]=EARLIER('Table'[Order])&& 'Table'[Discount Eligibility]=TRUE()&&'Table'[discount category]<>EARLIER('Table'[discount category])),'Table'[discount category])
    var _f=maxx(FILTER('Table','Table'[Order]=EARLIER('Table'[Order])&& 'Table'[Discount Eligibility]=FALSE() && 'Table'[discount category]<>EARLIER('Table'[discount category])),'Table'[discount category])
    return if(_t<>"","Yes",if(_f<>"","n/a"))
    pls see the attachment below
  • Ray_Minds's avatar
    Ray_Minds
    Icon for Solution Supplier rankSolution Supplier
    7 months ago

    Solution : 

    Answer:  The cleanest approach is to use a calculated column that checks, for the same Order, 
            whether a DEF row exists with Discount Eligibility = TRUE. 

      

    Calculated Eligibility = 
    VAR _Order = TableName[Order] 

      

    VAR _HasEligible = 
        CALCULATE ( 
            COUNTROWS ( TableName ), 
            TableName[Order] = _Order, 
            TableName[discount category] = "DEF", 
            TableName[Discount Eligibility] = TRUE 
        ) > 0 

      

    RETURN 
    IF ( 
        TableName[discount category] = "ABC" && _HasEligible, 
        "Yes", 
        "n/a" 
    ) 

      

      

    Result will be like: