Forum Discussion
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.
Thanks you all in advance
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 _ifFinally 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.Hi, TomasSys
To create a pie chart and show items when measure[_IsIn] is 1.
Result:Best Regards,
Community Support Team _ Zeon ZhengIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
- v-angzheng-msft
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 _ifFinally 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.Hi v-angzheng-msft ,
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áš
- v-angzheng-msft
Hi, TomasSys
To create a pie chart and show items when measure[_IsIn] is 1.
Result:Best Regards,
Community Support Team _ Zeon ZhengIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- AnkitKukreja
Super User
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áš
- AnkitKukreja
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.
