CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
yulGM
yulGM

Reputation: 1094

DAX Calculate Formula with both "AND" and "OR" filters (PowerBI)

Environment: PowerBI

I had a DAX formula that worked as expected when I had just one filter: # Bot Responses = CALCULATE([countUniqueActivityId], customEvents[EventName]="BotMessageSend" ).

However there is an additional filter I need to add, where it is not enough that one of 2 other conditions must be met: customEvents[cd_Text]<>"" or customEvents[cd_Attach]<>""

What is the correct syntax to have 'AND' and 'OR' conditions together?

I tried:

CALCULATE([countUniqueActivityId], customEvents[EventName]="BotMessageSend", OR(customEvents[cd_Text]<>"", customEvents[cd_Attach]<>""))

But that results in error:

A single value for column 'EventName' in table 'customEvents' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

EDIT: it doesn't show the error message anymore, but it doesn't return any values either

Upvotes: 1

Views: 2814

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

I recommend reading this article for more details but one possibility is the following:

CALCULATE (
    [countUniqueActivityId],
    customEvents[EventName] = "BotMessageSend",
    FILTER (
        ALL ( customEvents[cd_Text], customEvents[cd_Attach] ),
        OR ( customEvents[cd_Text] <> "", customEvents[cd_Attach] <> "" )
    )
)

Consider a KEEPFILTERS version if you want to add to the existing filter context rather than replace it.

CALCULATE (
    [countUniqueActivityId],
    KEEPFILTERS ( customEvents[EventName] = "BotMessageSend" ),
    KEEPFILTERS (
        FILTER (
            ALL ( customEvents[cd_Text], customEvents[cd_Attach] ),
            OR ( customEvents[cd_Text] <> "", customEvents[cd_Attach] <> "" )
        )
    )
)

Upvotes: 2

Related Questions