Multiple Filters in DAX COUNT (AND OR)

1
votes

I'm new to DAX and still trying to get the hang of the basics!

I'm stuck on a Power BI measure which needs to count the rows with the first filter, but only return a figure based on either the second or third filter. The below is not a valid syntax but demonstrates what I'm trying and failing to achieve with a AND OR statement.

In other words, count VIN in responses if Handover via app is 1 and either OPT In or OPT Out is 1.

Any thoughts how this can be done correctly?

CALCULATE(
    COUNTA('Responses'[VIN]),
    'Responses'[Handover Via App] IN { 1 } AND 'Responses'[OPT IN] IN { 1 } OR 'Responses'[OPT OUT] IN { 1 }
)

Any help would be appreciated!

2
powerbidax

2 Answers

1
votes

In DAX you can use && = AND, || = OR. So your measure would work as:

Measure = CALCULATE(
            COUNTA(Responses[VIN]),
                FILTER(Responses, Responses[Handover via App] = 1 &&
                (Responses[OPT IN] = 1 || Responses[OPT OUT] = 1)))
0
votes

DAX now allows for the OR operator || to be used in a boolean filter argument, so you can write

CALCULATE (
    COUNTA ( Responses[VIN] ),
    Responses[Handover via App] = 1,
    Responses[OPT IN] = 1 || Responses[OPT OUT] = 1
)

Multiple arguments are combined using AND logic.