cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CL1
Frequent Visitor

AND Logic Slicer

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:

CL1_0-1637613565602.png

 

Relationships:

CL1_1-1637613565606.png

 

Renovation Date Query:

CL1_2-1637613565600.png

 

Keyword List Query:

CL1_3-1637613565578.png

 

Sample of Data Query:

CL1_4-1637613565707.png

 

 

And the Calendar query is just your basic calendar setup.

3 REPLIES 3
v-stephen-msft
Community Support
Community Support

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.

vstephenmsft_1-1638941986569.png

vstephenmsft_2-1638942108023.png

You'll get this.

vstephenmsft_3-1638942195649.png

 

 

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

vstephenmsft_4-1638942254447.pngvstephenmsft_5-1638942264524.png

vstephenmsft_6-1638942269742.png

 

3.Create a relationship between tables.

vstephenmsft_7-1638942424433.png

 

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)

vstephenmsft_8-1638942638136.png

 

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

When you do not filter, all stores are displayed.

vstephenmsft_9-1638942764234.png

 

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

vstephenmsft_10-1638942847690.png

vstephenmsft_11-1638942853304.png

 

 

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.

 

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?

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!