CODEWITHSUNDEEP
powerbidax
BayscapeNewbie
BayscapeNewbie

Reputation: 81

Multiple Filters in DAX COUNT (AND OR)

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!

Upvotes: 2

Views: 24435

Answers (3)

Leonardo Fagundes
Leonardo Fagundes

Reputation: 1

Here is an example of a solution to the problem using more than one filter on different tables. Very simple just adding two filters.

CALCULATE(
    SUMX (
        FACT_VENDA,
        FACT_VENDA[Item_venda_qtde] * ([item_venda_vlr_sdesconto] + FACT_VENDA[item_venda_vlr_desconto]) + FACT_VENDA[item_venda_vlr_imposto]
    ),FILTER(
        FACT_VENDA,
        FACT_VENDA[nm_tipo_operacao] = {"VE"}
    ),FILTER(
        DIM_PRODUTO,
        DIM_PRODUTO[cod_cad_produto] = 1   
    )
)

Upvotes: 0

Alexis Olson
Alexis Olson

Reputation: 40204

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.

Upvotes: 0

ffffrank
ffffrank

Reputation: 544

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

Upvotes: 2

Related Questions