cancel
Showing results for
Did you mean:
Frequent Visitor

## Need to slice based on many values

Hello,

I have a table (see below), which I want to slice, so that I would get only rows for which all values specified in slicer would apply.

 Customer 1 Category 1 Trait 1 Price paid by customer 1 Customer 1 Category 2 Trait 2 Price paid by customer 1 Customer 1 Category 5 Trait 3 Price paid by customer 1 Customer 1 Category 7 Trait 4 Price paid by customer 1 Customer 2 Category 1 Trait 1 Price paid by customer 2 Customer 2 Category 5 Trait 3 Price paid by customer 2 Customer 2 Category 2 Trait 5 Price paid by customer 2 Customer 2 Category 7 Trait 6 Price paid by customer 2 Customer 2 Category 8 Trait 7 Price paid by customer 2 Customer 2 Category 9 Trait 8 Price paid by customer 2

I have hundreds of customers with hundreds of traits. I want to be able to choose multiple values in slicer-like visual and get only customers for which all selected traits apply. For example, here I would select Trait 1 and Trait 3 and get.

 Customer 1 Price paid by customer 1 Customer 2 Price paid by customer 2

and NOT

 Customer 1 2x Price paid by customer 1 Customer 2 2x Price paid by customer 2

Or if I choose Trait 1 and Trait 8 I would only get a table shown below, because only Customer 2 has both Trait 1 and 8.

 Customer 2 Price paid by customer 2

With regular slicer I would get both customers like this.

 Customer 1 Price paid by customer 1 Customer 2 2x Price paid by customer 2

Is there any easy solutions for that?

I know I also have Trait categories, which I could pivot, but that still is approximately 30 and creating 30 slicer on one page does not fit the purpose. I have found couple of solutions, however, those all involved creating a special slicer table with all categories specified in the code, but I would like that to be dynamic and automatic, as there may be categories added in the future.

2 ACCEPTED SOLUTIONS
Community Support

Hi, @TomasSys

Create a calculation table summarizing the names,

``Name_Item = SUMMARIZE(ALL('Data'),[Name])``

then create a measure as follows,

``````_IsIn =
var _N_Item=SUMMARIZE(ALLSELECTED('Name_Item'),[Name])
var _S_Item=SUMMARIZE(FILTER(ALL('Data'),'Data'[SN (order)]=MAX('Data'[SN (order)])),[Name])
var _IsIn=COUNTROWS(EXCEPT(_N_Item,_S_Item))
var _if=IF(_IsIn=0,1,0)
//1>>"Yes",0>>"No"
return _if``````

Finally the table visually displays items with measure equal to 1

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

Community Support

Hi, @TomasSys

To create a pie chart and show items when measure[_IsIn] is 1.
Result:

Best Regards,
Community Support Team _ Zeon Zheng

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

6 REPLIES 6
Community Support

Hi, @TomasSys

Create a calculation table summarizing the names,

``Name_Item = SUMMARIZE(ALL('Data'),[Name])``

then create a measure as follows,

``````_IsIn =
var _N_Item=SUMMARIZE(ALLSELECTED('Name_Item'),[Name])
var _S_Item=SUMMARIZE(FILTER(ALL('Data'),'Data'[SN (order)]=MAX('Data'[SN (order)])),[Name])
var _IsIn=COUNTROWS(EXCEPT(_N_Item,_S_Item))
var _if=IF(_IsIn=0,1,0)
//1>>"Yes",0>>"No"
return _if``````

Finally the table visually displays items with measure equal to 1

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

Frequent Visitor

this suggestion works perfectly. Allow me one more question. How do I make this selection work in pie chart? I have tried it, but was not able to force the pie chart to visualize results displayed in the table.

Thanks a lot

Tomáš

Community Support

Hi, @TomasSys

To create a pie chart and show items when measure[_IsIn] is 1.
Result:

Best Regards,
Community Support Team _ Zeon Zheng

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

Solution Supplier

Hi! @TomasSys

I understood your scenario now, but I don't have the data to work on the solution.

So, if you've few conditions like 4-6 then you can create an custom column with those conditions. Else, I would need a sample data to work on.

Thanks,
Ankit Kukreja
Solution Supplier

Thanks,
Ankit Kukreja
Frequent Visitor

Hello @AnkitKukreja ,

below I share a link to my sample data.

Expected result is to have a slicer in which I will choose any combination of traits from column C and will get a list (or any other visual) of SN (column A) and Revenue (column D) of those orders, for which chosen combination of traits apllies. Some combinations can get me every single SN+Revenue, as is may be something, that always goes with an order, but I mostly interested in combinations, which are rare. And currently I only get a list of orders (SN) containing Trait 1 OR Trait 2 OR Trait 3, etc. I need to get a result for Trait 1 AND Trait 2 AND Trait 3, etc.

Expected result base on the sample data.

1. If I choose Trait 1 and Trait 21, I will get

 SN Revenue 1 20 2 30 3 40 4 24

2. If I choose Trait 1, Trait 21 and Trait 41, I will only get one this.

 SN Revenue 1 20 2 30

Does this make sense?

Thanks a lot

Tomáš

Announcements

#### 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.

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt will talk about the importance of Data Modeling.

#### 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.