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 excluded 

 

Thanks,

 

Mikkel

Need Help

6 Replies

  • Stachu's avatar
    Stachu
    Community Champion
    8 years ago

    can't you just exclude all the customers that bought something from supplier C?
    Otherwise you can create a measure that would show Blank if supplier C was used

    • ff6d's avatar
      ff6d
      Helper I
      8 years ago

      Try the problem is that I do not know how to exclude those who have :) 

      • ff6d's avatar
        ff6d
        Helper I
        8 years ago

        I found a simple solution :) Thanks!

  • 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