Forum Discussion
Capturing Slicer Values from Multiple Slicers for a Single Column
So, here's an interesting one: can I use multiple slicers on a single column, capture those choices, and use them in measures?
Here's what I've been asked for:
| Item Code | Item Description | Unit Cost | No. of Units | Total Cost |
| 123456 | Concrete slab | £40 | 1 | £40 |
| 246864 | Large Wooden Box | £25 | 3 | £75 |
| 654321 | Plastic Sheeting | £10 | 5 | £50 |
They want to be able to select an item code from the first column, have the description and unit cost be populated, be able to enter the number of units, and have the total cost calculated. I can easily do this once: the No. of Units is a dropdown prepopulated with numbers to choose from, and the total cost is a simple measure mutiplying the unit cost and number. Simple.
But, they want to be able to do this about 10 times. Most of it can be set up using multiple slicers, cards, and the 'Edit interactions' function to limit which cards the slicers are interacting with...but I have no idea how to (or even if you can) capture the individual slicer results for referencing within measures to calculate totals. This sort of thing can be set up in Excel by small children wearing mittens, and I've said as such (maybe not about children and mittens), but they are insistent that a solution be found for PBI (I don't ask why, I'm just the Power BI Monkey).
I do have a solution: create 10 copies of the underlying dataset and link each calculated 'row' to a separate dataset. This will work, will produce the required results and, if I'm honest, will be enough to satisfy this particular person, but it feels clunky and over engineered.
Is there a more elegant solution?
This doesn't sound like something Power BI slicer is designed to do natively. Your solution of creating separate disconnected tables to separate slicers for each row sets would be a good workaround with native visuals.
If your audience wants Excel-like input table inside Power BI where each row has its own item selection and quantity, maybe you can give third party visuals like 'Inforiver' a try.
4 Replies
- Greg_Deckler
Community Champion
DNedry You can use something like:
VAR __Table = DISTINCT( 'Table'[Item Code] )
and then use the IN operator:
RETURN SUMX( FILTER( 'Table'[Item Code] IN __Table ), [Unit Cost] )
For example. Not entirely certain what you are trying to do exactly. But your disconnected table should also work.
- MasonMA
Super User
This doesn't sound like something Power BI slicer is designed to do natively. Your solution of creating separate disconnected tables to separate slicers for each row sets would be a good workaround with native visuals.
If your audience wants Excel-like input table inside Power BI where each row has its own item selection and quantity, maybe you can give third party visuals like 'Inforiver' a try.
Hi DNedry,
Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to MasonMA and Greg_Deckler for prompt and helpful responses.
Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.
Best regards,
Prasanna KumarHi DNedry,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.
Regards,
Community Support Team.
