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

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.

  • Stevianne's avatar
    Stevianne
    Frequent Visitor
    4 years ago

    Hi,

     

    I apllied the measure you created. Thanks it was very helpfull. Next step I want to make is to apply this measure "_count" in a table.  I want to measure the % of the total.

     

    total_sum__count = Sum(_count)   (I can't create this measure)

    %total =  _count divide total_sum__count

     

    Do you have any ideas how to solve this?