cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TomasSys
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 1Category 2Trait 2Price paid by customer 1
Customer 1Category 5Trait 3Price paid by customer 1
Customer 1Category 7Trait 4Price paid by customer 1
Customer 2Category 1Trait 1Price paid by customer 2
Customer 2Category 5Trait 3Price paid by customer 2
Customer 2Category 2Trait 5Price paid by customer 2
Customer 2Category 7Trait 6Price paid by customer 2
Customer 2Category 8Trait 7Price paid by customer 2
Customer 2Category 9Trait 8Price 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 1Price paid by customer 1
Customer 2Price paid by customer 2

and NOT

Customer 12x Price paid by customer 1
Customer 22x 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 2Price paid by customer 2

With regular slicer I would get both customers like this.

Customer 1Price paid by customer 1
Customer 22x 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

2 ACCEPTED SOLUTIONS
v-angzheng-msft
Community Support
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

vangzhengmsft_1-1642732367219.png

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.

View solution in original post

Hi, @TomasSys 

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

vangzhengmsft_0-1643009945093.png

 

 

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.

View solution in original post

6 REPLIES 6
v-angzheng-msft
Community Support
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

vangzhengmsft_1-1642732367219.png

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áš

Hi, @TomasSys 

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

vangzhengmsft_0-1643009945093.png

 

 

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.

AnkitKukreja
Solution Supplier
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
www.linkedin.com/in/ankit-kukreja1904
AnkitKukreja
Solution Supplier
Solution Supplier

Hi @TomasSys 

 

Please share your sample data and expected solution.

Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904

Hello @AnkitKukreja ,

 

below I share a link to my sample data.

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

SNRevenue

1

20
230

3

40
424

 

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

SNRevenue
120
230

 

Does this make sense?

 

Thanks a lot

 

Tomáš

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.

May 23 2022 episode 5 with aka link.jpg

The Power BI Community Show

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

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!