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 ent...
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"
)
Ray_Minds's avatar
Ray_Minds
Icon for Solution Supplier rankSolution Supplier
8 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: