Filter NOT and Include.

[Anonymous]

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,

1,

0

)

but it is giving issues while executing...

Anyone an idea to solve this??

thanks!!

M

[tamerj1 · Accepted solution]

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

[Anonymous]

hi Tamerj1

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

well done.

Mrt

[CNENFRNL]

Syntax equivalent

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

[Anonymous]

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.

[FreemanZ]

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

)

[Anonymous]

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

M