Keep filtered values on each row of a table

[Maria_Sanse]

Hi Community!

My question: is it possible to apply slicers and keep the filtered result in each row of a table/matrix, not removing it once the filter selection changes? Like accumulating the filter results?

(*

More details about my model. Any solution or alternative you can propose to me will be highly appreciated!

I have two slicers: products and quantities.

For every product, it's possible to select different quantities, which will be used to calculate a "Total cost of product".

My source file has all the possible combinations of products/quantities, with a column of Total cost (unit price*quantity selected). So it's about properly filtering the data (as far as I know, user of a PBI.com report cannot input parameters to be used in a calculation).

I want the user to select a portfolio of products (rows in the matrix), a quantity for each product (which can be different), and the total cost to be displayed inside the matrix (columns will be different rate types).

The issue is: user can select Product A in quantity 1. Then, when Product B is selected, quantity in the slicer keeps the previous value (1) or when it changes, it replaces completely the row in the matrix, giving each time one only row, and not accumulating a portfolio of products.

*)

Thanks in advance 🙂

[Greg_Deckler]

I have done some crazy stuff manipulating slicer behavior, see attached plus this AND slicer here: https://community.powerbi.com/t5/Quick-Measures-Gallery/Patient-Cohort-AND-Slicer/m-p/391883#M130

But what you are talking about does not seem possible to me, to essentially store the history of selections within a single slicer. Can you provide example data and expected results. And is there a "reasonable maximum" to product/quantity pairs? 

Even if you used list slicers and had multi-select, there would be no way to know the order in which someone selected the products and quantities.

[Maria_Sanse]

Hi Greg,

Thanks for your reply! I am looking at your dax code to see if I can use it in my model...

My reply to your questions: 

1. Can you provide example data and expected results?

This is a simplified model but represents well the idea (I changed the Products for Tools):

• Tools: A, B
• Tools have 2 rate types: stand-by and operating
• Each rate type has a value (depending on tool type): unit rate cost
• Client can choose Tool A in quantities: 1, 4, 8; Tool B in quantities: 2, 6, 10
• Total cost is dependent on all previous parameters.

Example: client can choose Tool A (row 1 in the matrix) in qty 4, input that will be used by the slicer to select the appropriate value of the columns (Stand-by and Operating costs). Also to choose Tool B (row 2 in the matrix) in qty 6.

My desired result in the PBI matrix: a diversified "cart" of tools and their costs, according to each quantity selected.

What I am currently getting by using two slicers ("Tools" and "Quantity"):

2. And is there a "reasonable maximum" to product/quantity pairs?

Total combinations: 51 pairs (not that bad...)

26 tools, each one with a different "string" of possible quantities (they repeat sometimes).

Maybe using slicers is not the best way to select the data I want to show in my matrix, maybe I have to give a different approach to my model... But my report is interactive, I want the user to be able of choosing different quantities so he can evaluate different scenarios, and not limit the model only to quantity 1 of each tool, which does not represents the reality.

Thanks again 🙂

[Greg_Deckler]

@Maria_Sanse  - Well perhaps you create slicer pairs for each product. So for example

Slicer 1 Product visual and Slicer 1 Quantity Visual these only have edit interactions between themselves and no other slicers on the page

Slicer 2 Product visual and Slicer 2 Quantity Visual. Same as above

Repeat this as necessary

Then, in your measure, you can grab the SELECTEDVALUE of these various slicers and then use those in your calculations.

You would almost certainly not want to have these slicers related to your fact table. 

Tough to say and this might be a big PITA but just a thought.

[Maria_Sanse · Accepted solution]

Hi guys @Greg_Deckler, @TomMartens

Thanks for your proposed solutions! I spent some time (days actually...) working around them. However, selecting both slicers, even when I limited the interactions between themselves, was still creating conflict with my data, and not displaying as I wanted in the matrix.

But... I have good news! A colleague from work gave me an idea, and it worked perfectly in my model. Hierarchy slicer: it is very simple solution, but I didn't know it even existed. So I leave it here in case someone finds it usefull:

Import from AppSource >Marketplace > Power BI Certified > HierarchySlicer

Thanks again for your suggestions!

Maria

[TomMartens]

Hey @Maria_Sanse ,

why not just create a table with a key column with these kind of values "A | 2" , "A | 4" , "A | 8", ...

Create this column also in your existing table.

This is what I would do, but maybe I'm missing something.

As @Greg_Deckler mentioned: history of slicer selections can not be preserved.

Regards,

Tom