AND logic for slicers

[tonyororke]

I have one slicer and one visual matix. . The slicer contains "features" and the matix contains "products" and "features". When i select multiple "features" from the slicer, I would like to apply AND logic so that only the "products" which contain all the "features" selected are displayed. 

When no "feature" is selected on the slicer, I'd like all "products" and "features" to be displayed on the matrix. 

Both "products" and "features" are from the one same dataset/table. 

Thanks in advance. 

[AllisonKennedy]

One option could be to do a simple COUNTROWS of the two selections and compare:

MEASURE=
VAR _CountSlicerFeatures = CALCULATE(COUNTROWS(VALUES(Table[Features])), ALLSELECTED(Table)
VAR _CountProductFeatures = COUNTROWS(VALUES(Table[Features]))
RETURN IF(_CountSlicerFeatures = _CountProductFeatures, 1)

Then use this as a filter for the matrix and set to show only when Measure =1

[tonyororke]

thanks for your reply. 

I tried the following measure and got an error saying the syntax for "VAR" was wrong.

AND filter =
VAR _CountSlicerFeatures = CALCULATE(COUNTROWS(VALUES('My products'[Feature])), ALLSELECTED('My products')
VAR _CountProductFeatures = COUNTROWS(VALUES('My Products'[Feature])
RETURN IF(_CountSlicerFeatures = _CountProductFeatures, 1)

To add some further context. 

The table name  is "My products" and has 3 columns 

Product

Feature

Value

The value colomn contains a mixutre of "YES", "NO" or numeric values. 

[AllisonKennedy]

@tonyororke UPDATED to show all when Slicer is not filtered: 

You were missing a couple close brackets )

I have also re-read your original post to see that the matrix contains features too, so have updated the measure to take that into account.

Upon reading that the Value column contains Yes, No and numbers, I'm wondering if this measure is accurate though - how do you know if a product has a selected feature?

AND filter =

VAR _TotalFeatures = COUNTROWS(ALL('My products'[Feature]))

VAR _CountSlicerFeatures = CALCULATE(COUNTROWS(VALUES('My products'[Feature])), ALLSELECTED('My products'))

VAR _CountProductFeatures = CALCULATE(COUNTROWS(VALUES('My Products'[Feature])), ALLSELECTED('My products'[Feature]))

RETURN IF(_CountSlicerFeatures=_TotalFeatures,1, IF(_CountSlicerFeatures = _CountProductFeatures, 1,0))

[tonyororke]

Thanks Alison - I tried with the updated syntax but it doesn't provide the outcome that I'm looking for.. 

Sorry - I should have provided some more detail on the values. 

in terms of the values - I want to remove anything which has No", so i suppose the filter would need to be "is not = "No". 

I have tried to show what I'm trying to achieve in more detail below. 

[AllisonKennedy]

To make this work you will need a separate 'FeatureSlicer' table that has no relationship to the matrix data, so that you can still display all the features, not just the selected features. You can create that directly in the data model (my recommendation) or use DAX.

Once you have that table, use it for the slicer.
With a small change to the measure supplied by @V-linl-msft you can use;
Measure = IF(SELECTEDVALUE('Table'[Feature]) IN VALUES('FeatureSlicer'[Feature])&&MAX('Table'[Value])="N","HIDE",SELECTEDVALUE('Table'[Value]))

And filter the matrix to show values with Measure not equal to Hide.

Hope that makes sense. Let us know if it works!

[tonyororke]

HI - thanks for getting back to me. So to check my understanding;.

My original table is "My Product"

I create a new table called "slicer product"using  the following dax  - slicer product  = 'My Poduct'

I use the column "slicer product [feature] in the slicer and do not add any relationships to other tables. 

I create a measure under the "My Product" table as below;

Measure = IF(SELECTEDVALUE('My Product'[Feature]) IN VALUES('Slicer Product'[Feature])&&MAX('My Product[Value])="NO","HIDE",SELECTEDVALUE('My Product'[Value]))

My objective with the above is to achieve the following-

No selctions made on slicer 

Feature 1 and Feature 3 selected on slicer. The result is that only "Product 1" is visible on the matrix as it is the product which does not have "NO" to either feature selected on the slicer. 

Are the steps above correct?

Thanks again

[AllisonKennedy]

You should only need:
slicer product= Values('My Product'[Feature])

For your slicer product table.

You may also need to add the bit for getting all products displayed when nothing is selected in slicer: 

MEASURE = 

IF(ISFILTERED('Slicer Product'[Feature]), IF(SELECTEDVALUE('My Product'[Feature]) IN VALUES('Slicer Product'[Feature])&&MAX('My Product[Value])="NO","HIDE",SELECTEDVALUE('My Product'[Value])), SELECTEDVALUE(My Product'[Value]))

But otherwise, yes, those steps should work.

[tonyororke]

Hi Alison - I have done as you suggsted above.
 - Created a table to show the product feature using the DAX slicer product = DISTINCT(My Product[Feature]). I did not add any relationship between the new and existing tables. 
 - Added 'slicer product'[Feature] to the slicer
 - Created a measure in the "My Product" table using the DAX

Measure =
IF(ISFILTERED('Slicer Product'[Feature]),
IF(SELECTEDVALUE('My Product'[Feature])
IN VALUES('Slicer Product'[Feature])&&MAX('My Product[Value])="NO","HIDE",
SELECTEDVALUE('My Product'[Value])),
SELECTEDVALUE(My Product'[Value]))

I then added to the above measure as a visual level filter on the matrix and used the condition "is not = HIDE"

Unfortunatlely the outcome was that the slicer does not filter the matrix. 

[V-lianl-msft]

Hi @tonyororke ,

I think the corresponding values can only be filtered at the row level. I don't have a perfect solution to this issue.

My alternative is to create a new "feature" table as a slicer, and then create the measure:

Measure = IF(MAX('Table'[Feature]) IN VALUES('Table 2'[Feature])&&MAX('Table'[Value])="N",BLANK(),MAX('Table'[Value]))

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tonyororke]

Hi Liang - many thanks for your reply. I have tried your solution but unfortunatley it doesn't meet the use case.