AND Logic Slicer

CL1 · ‎11-22-2021

Hi There,

I am trying to get a keyword slicer that uses AND logic. Basically I have sales data from different stores before and after a renovation date and each renovation has keywords attached to it. For the keyword slicer I would like it to use AND logic so that if multiple keywords are selected that only the stores with all the selected keywords show up and when nothing in the slicer is selected that all the data will show up. The keywords will be seperated by commas if there are multiple for a renovation. Below are some screen shots of sample data and how I have my data setup.

Thanks for the help.

Dashboard:

Relationships:

Renovation Date Query:

Keyword List Query:

Sample of Data Query:

And the Calendar query is just your basic calendar setup.

v-stephen-msft · ‎12-07-2021

Hi @CL1 ,

You can try to split the Keyword List of the main table first, and then unpivot them.

Here are the detailed steps.

1.Split as follows. If there is no space between words except for comma in your data, do not add space.

You'll get this.

2.Select three columns at the same time, then Unpivot, and then delete unnecessary columns.

3.Create a relationship between tables.

4.Create a measure. Put it into visual level filter, set show items when the value is 1.

Measure = var _count=COUNTROWS(FILTER(ALLSELECTED('Table'),[Store]=MAX('Table'[Store])))
var _count1= CALCULATE(COUNT('Table'[Store]),ALLEXCEPT('Table','Table'[Store]))
return IF(_count=_count1,1)

5.Here is the result. Create a slicer with keyword list from Table(2).

When you do not filter, all stores are displayed.

When you filter, only those that meet the filter criteria will be displayed.

Best Regards,

Stephen Tao

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

CL1 · ‎12-08-2021

Hi Stephen,

Thank you for your response!

This seems to be working for me but there is still one issue im seeing. When you select multiple keywords only the results with exactly those keywords show up. What i mean by this is if Store X had keywords blue and black, while Store Y had keywords blue, black and green, when i select blue and black in my slicer only Store X shows up even though Store Y also fits the criteria just with the extra keyword of green as well. Any idea how this could be fixed?