Slicing out values from a visual

[Anonymous]

I'm looking to see if it is possible to set up a line chart and a bar chart to slice out columns as needed from the visuals. My end-user is wanting to see the values of particular cash buckets over time, but those buckets exist as three separate columns within a single table. So for each location and day combination, I have three values I need to portray on a line chart and a bar chart. Showing all three is easy enough, but they want to be able to slice out particular buckets to remove them from the visual.

I can't find a way to slice out any of the three columns, bookmarks would get messy with all of the combinations possible, and cross-highlighting doesn't seem to work for this setup.

Does anyone have any ideas on how this ask might be implemented or if it is possible?

[Anonymous · Accepted solution]

I was able to make something work by unpivoting the table in Power Query based on the given buckets, then using a slicer to remove buckets as usual. It's a bit of a messy fix as I had to create a duplicate of my table, but it worked in the end.

[selimovd]

Hey @Anonymous ,

why don't you use a slicer and only return a value when a specific bucket is selected.

When the bucket is not selected you return BLANK(), then the line will not show.

Would that be an option?

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

I have a binary field for each bucket to indicate if there ar dollars for that line, but I'm hoping to avoid a Nested If statement for every possibility if at all possible, and can't justify a radical change to the data structure for such a minor request to unpivot the three columns.

Much appreciated though.

[selimovd]

@Anonymous Can you maybe show how your data is structured, your data model and what exactly you want to archive. I'm not sure if I fully understood your requirements.

Thank you and best regards

Denis

[Anonymous]

Here's an example of the table I have. The relevant parts of the data model are only a calendar table and location table.

I need to track cash as a bar chart and days as a line chart, and the end-user is wanting to be able to cut out group 1 (for example) and only see group 2 and 3. I could conceiveably write a nasty Nested If statement to return each combination, but again, I'd really like to avoid that.

Date	Location	Cash 1	Days 1	Cash 2	Days 2	Cash 3	Days 3
7/1/2021	5434	-51.41	1	0	0	156	1
7/2/2021	5434	15,874	1	13,845	1	0	0