Dynamically filter entities that contain ALL the values from slicer selection.

[Anonymous]

Hello friends,

I have a simple table with three columns: Customer, Product, Amount

Here is the challenge:

I would like to choose in a slicer SEVERAL products and i want to see only the customers that have ALL of those chosen Products.

In other words, I would like the slicer to work as AND filter instead of OR...

Any ideas would be welcomed!

Thank you,

Michael

[Eric_Zhang · Accepted solution]

---

@Anonymous wrote:

Hello friends,

I have a simple table with three columns: Customer, Product, Amount

Here is the challenge:

I would like to choose in a slicer SEVERAL products and i want to see only the customers that have ALL of those chosen Products.

In other words, I would like the slicer to work as AND filter instead of OR...

Any ideas would be welcomed!

Thank you,

Michael

---

@Anonymous

Maybe you can play some trick with a measure?

IsShown =
IF (
    ISFILTERED ( 'Table'[product] )
        && DISTINCTCOUNT ( 'Table'[product] )
            = CALCULATE ( DISTINCTCOUNT ( 'Table'[product] ), ALLSELECTED ( 'Table' ) ),
    1,
    BLANK ()
)

[Anonymous]

Great @Eric_Zhang,

Many thanks!

[Greg_Deckler]

I did something similar to this but it was not dynamic. What I did was create a table of my Products and assigned a number to each of them like:

1

10

100

I could then filter my table to only be the values that had a SUM of "111". Worked great, but not sure if you could turn this into a dynamic solution. I'll think about it. Be interested if someone has a solution.