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