topic Filter NOT and Include. in DAX Commands and Tips

Filter NOT and Include.

Anonymous — Fri, 10 Mar 2023 08:00:27 GMT

hi,

in Cognos i have a filter that looks like this:

Fact_PO NOT([PO Type] includes ("INT", "IN2") AND [Status] includes ('10','15','31')

In PBI webversion it is apparently not possible to make such filter without using a DAX statement.

So, i have tried:

IF(

COUNTROWS(

FILTER(

FACT PO,

FACT PO[PO TYPE]<> "INT" &&

FACT PO[PO TYPE]<> "IN2" &&

(FACT PO[STATUS]=10 || FACT PO[STATUS]=15 || FACT PO[STATUS]=31)

)

) >0,

)

but it is giving issues while executing...

Anyone an idea to solve this??

thanks!!

Re: Filter NOT and Include.

FreemanZ — Fri, 10 Mar 2023 08:36:11 GMT

hi Anonymous

try like:

IF(

COUNTROWS(

FILTER(

FACT PO,

AND(

NOT FACT PO[PO TYPE] IN{"INT", "IN2"},

FACT PO[STATUS] IN {10, 15, 31}

)

)>0, 1, 0

)

Re: Filter NOT and Include.

CNENFRNL — Fri, 10 Mar 2023 08:48:56 GMT

Syntax equivalent

Flag = 1 - CALCULATE( ISEMPTY( Fact_PO ), NOT ( Fact_PO[PO Type] IN { "INT", "IN2" } && Fact_PO[Status] IN { 10, 15, 31 } ) )

Re: Filter NOT and Include.

Anonymous — Fri, 10 Mar 2023 09:08:06 GMT

HI Freeman,

thanks for your swift response, appreciate it!

Unfortunately i get an error messag: syntax or semantic error: at line.... Too many arguments were passed by the COUNTROWS Function. The maximum argument count for the function is 1.

Any alternatives?

thanks!!

Re: Filter NOT and Include.

Anonymous — Fri, 10 Mar 2023 09:21:30 GMT

HI,

thanks for the swift response, appreciate it.

unfortunately the proposed statement genereates an error:

syntax or sematic error at line.... Function CONTAINSROW does not support comparing values of type integer with values of type text. Consider using the VALUE or FORMAT function to convert one of the values.

Re: Filter NOT and Include.

tamerj1 — Fri, 10 Mar 2023 11:12:46 GMT

Hi Anonymous

please try

Measure =
INT (
ISEMPTY (
FILTER (
'Fact PO',
NOT ( 'Fact PO'[PO Type]
IN { "INT", "IN2" }
&& 'Fact PO'[Status] IN { "10", "15", "31" } )
)
)
)

Re: Filter NOT and Include.

Anonymous — Fri, 10 Mar 2023 11:41:10 GMT

hi Tamerj1

thanks for helping out, it seems to be working!!!!

well done.

Mrt