Forum Discussion

Stevianne's avatar
Stevianne
Frequent Visitor
5 years ago
Solved

advanced filtering

Hello Powerbi community,

 

I need some help with advanced filtering. I created a small example. When I filter on Bananas I do not only want to see 

A, B and D I also want to see A bought 5 peaches just like B bought 4 peaches. When filtering on Apples I want to see C bought 4 buth also bought 2 peaches.

So when filtering on a certain fruit I also want to see the other fruits that might be bought by A B C or D.

 

        Bananas    Apples     Peaches

A         4              0               5     

B         3              0               4

C         0              4               2

D         2              0              0

 

Which advanced filtering is needed to achieve this? thanks for your help!

 

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi Stevianne ,

    Extract the fruit column as a single calculated table and use it as a the slicer:

    Fruit = DISTINCT('Table'[Fruit])

    Create a measure like this:

    _count = 
VAR tab =
    FILTER (
        'Table',
        CALCULATE (
            SUM ( 'Table'[Count] ),
            FILTER (
                ALLEXCEPT ( 'Table', 'Table'[Category] ),
                'Table'[Fruit] = SELECTEDVALUE ( Fruit[Fruit] )
            )
        ) > 0
    )
RETURN
    SUMX (
        FILTER (
            tab,
            [Category]
                IN DISTINCT ( 'Table'[Category] )
                    && [Fruit] IN DISTINCT ( 'Table'[Fruit] )
        ),
        [Count]
    )

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Stevianne , first of all, these fruits need to be in rows. means you need you have un pivoted dhttps://radacad.com/pivot-and-unpivot-with-power-bi

     

    You need independent fruit table

    fruit = distinct(Table[fruit])

     

    Then a measure like 

    measure  =

    var _user = summarize(filter(Table, Table[Fruit] in allselected(Fruit[Fruit])), Table[User])

    return

    calculate(sum(Table[Qty]), filter(Table, Table[User] in _user)) 

     

     

     

    • Stevianne's avatar
      Stevianne
      Frequent Visitor
      5 years ago

      Hi,

       

      Thanks for your feedback. I tried your option. I'am I right you create

       

      - table for fruit (apples, bananas, peaches)

      - table for user (A,B,C and D)

      - table with quantity? (how to creatie this one?)

       

       

      for me it would be helpfull if you are able to make an example in PBI, is that possible?

       

      thanks a lot so far!

       

      • v-yingjl's avatar
        v-yingjl
        Community Support
        5 years ago

        Hi Stevianne ,

        Extract the fruit column as a single calculated table and use it as a the slicer:

        Fruit = DISTINCT('Table'[Fruit])

        Create a measure like this:

        _count = 
VAR tab =
    FILTER (
        'Table',
        CALCULATE (
            SUM ( 'Table'[Count] ),
            FILTER (
                ALLEXCEPT ( 'Table', 'Table'[Category] ),
                'Table'[Fruit] = SELECTEDVALUE ( Fruit[Fruit] )
            )
        ) > 0
    )
RETURN
    SUMX (
        FILTER (
            tab,
            [Category]
                IN DISTINCT ( 'Table'[Category] )
                    && [Fruit] IN DISTINCT ( 'Table'[Fruit] )
        ),
        [Count]
    )

         

        Best Regards,
        Community Support Team _ Yingjie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.