cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

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


@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 ()
)

Capture.PNG

Capture.PNG

 

Capture.PNG

View solution in original post

3 REPLIES 3
Highlighted
Super User IX
Super User IX

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

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Microsoft
Microsoft

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


@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 ()
)

Capture.PNG

Capture.PNG

 

Capture.PNG

View solution in original post

Highlighted
Anonymous
Not applicable

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

Great @Eric_Zhang,

Many thanks!

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors