Forum Discussion
Helper IIAND logic for slicers
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.
10 Replies
- AllisonKennedy
Community Champion
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
tonyororkethanks 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))
