Slicer/Filter Challenge -I'm losing it

[Anonymous]

"Stuck on  solving an issue is the BEST FEELING EVER" - said no one ever 

In all seriousness, I'm needing a savior right now, and. I'm hoping a Clark Kent "SuperMicosoftPowerBIMan/Woman" can save me from this slicer/filter nightmare I'm being delt with.

My sales team discovered power bi, and they got... very excited. They are wanting a report that pretty much gives our clients the experience as if they are shopping online - allowing the client to "pick and choose" products and utilmately "view their shopping chart" to see how the list of products impacts the client's current budget. 

I was given a data table that was pretty doable to work with. Columns consisted of Category, Subcategory, Solution, and the Financial Impact (Percent discount off the total budget). To make make the Financial Impact Column more realiistic, I went ahead and created some simple measures and what if paramters. So far, cake. 

From the data table given to me, I had to create some supporting tables for the slicers. So I created a "Category and a "Subcategory" slicer. Looks something like this:

Category Slicer Name/ Category ID

Category 1/ 1

Category 2/2

Category 3/3

Catagory 4/4

(7  Unique Categories In All) 

Subcategory Name/ Subcategory ID

Sub A/ A

Sub B/ B

Sub C/ C

Sub D/ D

17 Unique Subcategories in All... so basically some Categories will have 1 subcategory, others might have 3

then there's the Fact Table that they connect to:

Category ID/SubCategoryID/ Product Name/ $ 

(each sub category contains about 10-15 unique products)

I went ahead and showed them the dashboard I created - the were able to select which Categories they wanted to look into, which subcategories to look into futher (from the categories the are connected to), and finally, they saw a filtered list of products that came from the filtered down Categories and Subcategories, and was able to successfully choose the products. Just. Like. They. Wanted.

However..... they had some feedback. The End Result "list of products" based on the Categories they selected and subcategories they selected was too long... and it wasn't easy on the eye.. meaning that it was just a list of products... at the end of the day they want to have that list broken out by Category. Like Most, I just used the Hierarchy Slicer to do so. However, the sales team didn't want just a single list broken out by Category. They wanted individual lists. 

So instead of the filtered down by Category and Subcategory product list... that would look like this

• Category1
  • SubA
    • Lexus
  • SubB
    • Mustache
• Category2
  • SubF
    • McDonalds
      • Magic Invisible Stuff

They wanted individual slicers pretty much 

 One Slicer that shows only Category 1 filtered list of Products

• Category1
  • SubA
    • Lexus
  • SubB
    • Mustache

One Slicer that shows only Cateory 2 Filtered list of Products

Here's what I've tried to do to replicate that distinct request:

Create a Page for each Category, apply a page level filter, then sync slicers onto a "Output" page - Failed. Values ended up canceling eachother out (just got blank values) 

Created fact tables by Category... this worked... but I prefer to not do this... and I haven't been successful at using the UNION and ROW functions to created a calculated table to reflect the text values selected, as well as their % value by row. 

I really really really hope I don't get the a response that says " send the pbix", because I would have to create a complete dummy pbix because of the proprietary information the true report has. 

Anyone want to take a stab and throw an idea on the wall? 

Much Appreciated!

[PANDAmonium]

It's not ideal, but what about merging your data and facts tables and then creating reference tables with a filter on each table for your seven categories. You should end up with 7 tables for each of your categories.

This will allow you to seperate out your category slicers without them interfering with each other along with being able to create your individual category lists. And the rest of it, it sounds like you already know.

The downside of course would be that if they ever add or remove a category ...

[Cmcmahan]

It seems like the biggest problem is that your sales people want to use PowerBI as a screwdriver to solve their problem involving nails.  Also as much as you would hate it, sending the .pbix would be super helpful here.  Even mocking up 2-3 categories worth of items (with 1-3 subcategories ea) would be useful, we don't need all 7.  Heck, just changing the names of categories/products and randomizing the cost would be fine.

It might be possible to create 7 "results" pages and set a page level filter on each of them for each category. You could potentially show more visuals on data relating to that specific category to fill some of the space.  You could use drill down bookmarks to move from a cart overview page to each category's page, or however you want navigation to work.

You may also be able to set up a matrix that has collapsible rows for your final view.  Then they can expand/collapse categories and subcategories as desired to keep the page from looking cluttered.

At the end of the day, this seems like a square peg, round hole situation.  Is it possible to use Power BI to show all sorts of data visualizations, but unless you want to get into the habit of writing your own custom visualizations, I would tell them that Power BI has severe limitations as a shopping cart application, and this is one of them.

[Anonymous]

Preach! It would be ideal to just use power apps for this.

[mussaenda]

My suggestions:

1. use the normal slicer.

You can use 3 slicers.

One for the Category, one for sub, and one for the detailed one.

Once you choose in Category Slicer, the slicers for the subcategory, and detailed category will also be filtered.

2. Use one normal slicer and One heirarchy Slicer

One normal slicer for the Category,

and One Heirarchy for the Sub Category and the detailed category.

Same concept as the number 1.

You can also set the multi select on the slicers you want.

This is, if you are open to use more than one slicer.

[Anonymous]

Thanks for the response, can’t mark this solved though. Yes, very aware of the slicer tip you gave me.