Slicing across characteristics where each item could have many, some, or no characteristics

[HenryCrun]

Say I have a map of businesses. I want to filter the map, say with a slicer, to businesses that provide a certain service. Say my data looked like this:

BusinessName	Location	DoesGiftWrapping	DoesPetCare	DoesCarCleaning	DoesPhoneRepair
ABC Inc	[address]	TRUE	TRUE	FALSE	FALSE
DEF Ltd	[address]	FALSE	TRUE	FALSE	FALSE
QRS and Co	[address]	FALSE	TRUE	FALSE	TRUE
XYZ Industries	[address]	TRUE	FALSE	TRUE	FALSE

I want a set up where the end user can select one service – say, gift wrapping – and the map will only show businesses that do gift wrapping (in this case, where DoesGiftWrapping is true). In my real example, there are thirteen potential services any business could provide. 

How do I begin to go about this?

[Irwan · Accepted solution]

hello @HenryCrun 

i would do something like this.

1. unpivot the data

2. create a calculated column for filter/slicer that only for True value

3. create table and slicer visual (the slicer contain value from the previously made calculated column).

4. remove (Blank) value in slicer. This appears because of the if statement in calculated column.

Hope this will help.

Thank you.

[v-cgao-msft · Accepted solution]

Hi @HenryCrun ,

Provide a way to possibly if more than one choice is made.

The first step is to unpivot the data like Irwan suggested.

Then please create a new measure like:

Measure = 
VAR __cur_location = SELECTEDVALUE('Table'[Location])
VAR __locations = CALCULATETABLE(VALUES('Table'[Location]),'Table'[Value]=TRUE())
VAR __true_count = CALCULATE(DISTINCTCOUNT('Table'[Attribute]),'Table'[Value]=TRUE())
VAR __selected_count = COUNTROWS(ALLSELECTED('Table'[Attribute]))
VAR __result = IF(__cur_location IN __locations && __true_count=__selected_count, 1)
RETURN
    __result

Apply it the visual's filter:

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Irwan · Accepted solution]

hello @HenryCrun 

i would do something like this.

1. unpivot the data

2. create a calculated column for filter/slicer that only for True value

3. create table and slicer visual (the slicer contain value from the previously made calculated column).

4. remove (Blank) value in slicer. This appears because of the if statement in calculated column.

Hope this will help.

Thank you.