Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Filter NOT and Include.

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's avatar
    tamerj1
    3 years ago

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

6 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      hi Tamerj1

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

      well done.

      Mrt

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    3 years ago

    Syntax equivalent

    Flag = 
1 - CALCULATE(
        ISEMPTY( Fact_PO ),
        NOT ( Fact_PO[PO Type] IN { "INT", "IN2" } && Fact_PO[Status] IN { 10, 15, 31 } )
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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.