Forum Discussion

ff6d's avatar
ff6d
Helper I
8 years ago

Using Conditions when filtering

Hi,    I want to filter on shops in my data set if they ONLY used supplier A and B and not C. E.g. if the shop used supplier A for 50 euros, B for 20 euros and C for 1 euros this shop should be exc...
Need Help
v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi ff6d,

 

If you want to filter out rows in data view, please create a calculated table as below:

Table =
FILTER (
    Sheet1,
    CALCULATE (
        COUNT ( Sheet1[euros] ),
        FILTER ( ALLEXCEPT ( Sheet1, Sheet1[Shops] ), Sheet1[Supplier] = "C" )
    )
        = 0
)

 

If you want to filter out rows in visual view, please create such a measure, and add it to visual level filter, set its value to "is not blank".

flag1 =
IF (
    CALCULATE (
        COUNT ( Sheet1[Supplier] ),
        FILTER ( ALLEXCEPT ( Sheet1, Sheet1[Shops] ), Sheet1[Supplier] = "C" )
    )
        > 0,
    BLANK (),
    1
)

 

Best regards,

Yuliana Gu

  • ff6d's avatar
    ff6d
    Helper I
    8 years ago

    Hi Yuliana,

     

    When I use your measure my data filters on those who only use supplier C. Can you figure out how I can "reverse" the measure?

     

    Thanks! 

     

    Regards,

    • v-yulgu-msft's avatar
      v-yulgu-msft
      Microsoft Employee
      8 years ago

      Hi ff6d,

       

      As you can see the result is correct in my test. Please make sure you wrote the correct DAX formula for above measure. Besides, check whether you set the right value for "Visual level filter", it should be "Is not blank".

      flag1 =
IF (
    CALCULATE (
        COUNT ( Sheet1[Supplier] ),
        FILTER ( ALLEXCEPT ( Sheet1, Sheet1[Shops] ), Sheet1[Supplier] = "C" )
    )
        > 0,
    BLANK (),
    1
)

      To help you "reverse" the measure, please share sample data and the actual measure formula you used which can better describe your scenario.

       

      Regards,

      Yuliana Gu