DAX Calculate filter fonction

[eliottbbedard]

Hi everyone,

I've been using the following function for years and recently, the result is not returning the expected value.

My formula is pretty simple, I'm just counting the number of rows in a table and adding a filter to exclude 2 different statuses (from the same column in the same table) using the OR (||) symbol. It was working as expected until few weeks but now the filter function is not excluding the specified rows.

AppReceived = 

VAR A = 
CALCULATE(
    COUNTROWS(Current_ProjectList),
    Current_ProjectList[Project Status \ État du projet] <> "Abandoned" || Current_ProjectList[Project Status \ État du projet] <> "Withdrawn",
    USERELATIONSHIP(Current_ProjectList[RegionID],'Region Table'[RegionID]
))

Return IF(A=BLANK(),0,A)

Strangly, when I change the operator for AND (&&) the result is giving me the expected value.

AppReceived = 

VAR A = 
CALCULATE(
    COUNTROWS(Current_ProjectList),
    Current_ProjectList[Project Status \ État du projet] <> "Abandoned" && Current_ProjectList[Project Status \ État du projet] <> "Withdrawn",
    USERELATIONSHIP(Current_ProjectList[RegionID],'Region Table'[RegionID]
))

Return IF(A=BLANK(),0,A)

I have a lot of calculations using this methodology has it was working in the past and I would like to avoid having to modified all of them, so I'm trying to find what could be the problem. Maybe some kind of update in DAX?

Any help will be much appreciated.

Thanks!

[johnt75 · Accepted solution]

It seems to me that the && logic is actually correct. If you consider the || logic, when a status is Abandoned then 

Current_ProjectList[Project Status \ État du projet] <> "Withdrawn"

will return true, as the status is not Withdrawn. And the same is true vice versa, when the status is Withdrawn then it is not Abandoned.

You could make the exclusion more specific by using

AppReceived =
VAR A =
    CALCULATE (
        COUNTROWS ( Current_ProjectList ),
        NOT Current_ProjectList[Project Status \ État du projet]
            IN { "Abandoned", "Withdrawn" },
        USERELATIONSHIP ( Current_ProjectList[RegionID], 'Region Table'[RegionID] )
    )
RETURN
    IF ( A = BLANK (), 0, A )