Earlier this week I received an email asking for help with a Power Pivot model. The issue was that the individual had built a model, and wanted to add slicers for value fields. In other words, they’d built the DAX required to generate their output, and wanted to use those values in their slicers. Which you can’t do. Except maybe you can… 🙂
My approach to solve this issue is to use Power Query to load my tables. This gives me the ability to re-shape my data and load it into the data model the way I need it. I’m not saying this is the only way, by any means, but it’s an approach that I find works for me. Here’s how I looked at it in Excel 2013. (For Excel 2010 users, you have to run your queries through the worksheet and into Power Pivot as a linked table.)
Background
The scenario we’re looking at is a door manufacturer. They have a few different models of doors, each of which uses different materials in their production. The question that we want to solve is “how many unique materials are used in the construction of each door?” And secondarily, we then want to be able to filter the list by the number of materials used.
The first question is a classic Power Pivot question. And the setup is basically as follows:
- Download the sample file here
- Use Power Query to pull the MaterialsList table (shown below) into Power Pivot.
- Create a PivotTable with models on rows and material on columns
- Create a DAX measure to return the distinct count of materials:
- DistinctMaterials:= DISTINCTCOUNT(MaterialsList[material])
- Add a little conditional formatting to the PivotTable if you want it to look like this:
The secret to the formatting is to select the values and set up an icon set. Modify it to ensure that it is set up as follows:
Great stuff, we’ve got a nice looking Pivot, and you can see that our grand total on the right side is showing the correct count of materials used in fabricating each door.
Creating Slicers For Value Fields
Now, click in the middle of your Pivot, and choose to insert a slicer. We want to slice by the DistinctMaterials measure that we created… except.. it's not available. Grr…
Okay, it’s not surprising, but it is frustrating. I’ve wanted this ability a lot, but it’s just not there. Let’s see if we can use Power Query to help us with this issue.
Creating Queries via the Editor
We already have a great query that has all of our data, so it would be great if we could just build a query off of that. We obviously need the original still, as the model needs that data to feed our pivot, but can we base a query off a query? Sure we can!
- In the Workbook Queries pane, right click the existing “MaterialsList” query and choose Edit.
- You’ll be taken into the Power Query editor, and on the right side you’ll see this little collapsed “Queries” window trying to hide from you:
- When you expand that arrow, you’ll see your existing query there!
- Right click your MaterialsList query and choose “Reference”.
You’ve now got a new query that is referring to your original. Awesome. This will let us preserve our existing table in the Power Pivot data model, but reshape this table into the format that we need.
Building the Query we need
Let’s modify this puppy and get it into the format that will serve us. First thing, we need to make sure it’s got a decent name…
- On the right side, rename it to MaterialsCount
Now we need to narrow this down to a list of unique material/model combinations, then count them:
- Go to Add Column –> Add Custom Column
- Leave the default name of “Custom” and use the following formula: [model]&[material]
- Sort the model column in ascending order
- Sort the material column in ascending order
We’ve not got a nicely ordered list, but there’s a few duplicates in it.
Those won’t help, so let’s get rid of them:
- Select the “Custom” column
- Go to Home –> Remove Duplicates
Now, let’s get that Distinct Count we’re looking for:
- Select the “model” column
- Go to Transform –> Group By
- Set up the Group By window to count distinct rows as follows:
Very cool! We’ve now got a nice count of the number of distinct materials that are used in the production of each door.
The final step we need to do in Power Query is load this to the data model, so let’s do that now:
- File –> Close & Load To…
- Only create the connection and load it to the Data Model
Linking Things in Power Pivot
We now need to head into Power Pivot to link this new table into the Data Model structure. Jump into the Manage window, and set up the relationships between the model fields of both tables:
And that’s really all we need to do here. Let’s jump back out of Power Pivot.
Add Slicers for Value Fields
Let’s try this again now. Click in the middle of your Pivot and choose to insert a slicer. We’ve certainly got more options than last time! Choose both fields from the “MaterialsCount” table:
And look at that… we can now slice by the total number materials in each product!
9 thoughts on “Slicers For Value Fields”
Nice solution Ken.
Well thought out
Thanks Roger!
Great idea. I'm trying to figure out if this would work if I needed to filter by time period however. If the original material list had a date column (e.g. model 207a used 4 materials in 2014 but only 3 in 2015) what would your slicer return?
I have a similar question for our hospitals. I have convoluted measures to determine which medical items are used in which hospitals, and it's important to be able to list out which items are used in 4, which in only 3, etc. I'd love to have a slicer like that to quickly pull the results but I'm not sure how robust it would be if I needed to filter by year, manufacturer, and other fields.
GMF It would probably be best to post this question in our forum at http://www.excelguru.ca/forums. You can upload a sample workbook and someone can help you through the issue.
Hello,
I have a main query (2.3 mio lines). I want to do a group to sumup values according to one column (a bit like a sumif in Excel).
To do so, I duplicate my main query (that contains already many steps) and launch my group by. The value of this group by is reused later in the main query as a merge query to add the new calculated values to my table.
The issue is that now, going to the power query editor and refreshing takes me 30 minutes ! (I have a good PC with 64 gb ram and windows/Excel 64)
(I have 2 group by based on 2 duplicates of the main query, I suppose that is what creating the slow downs).
If I use a reference instead of a duplicate, would it go faster (i.e. no need to recalculate all of the query steps each time) ? Is there another way to reuse the main query for a group by ? Is there another way than using group by (which seems to use all my resources) to do sumif equivalent ?
Thanks
Hey Erik,
I think in this case we'd need to see your query. Can you post a question in our forum and include a sample workbook? http://www.excelguru.ca/forums
Hi Ken,
With Power BI, these slicers features are ready-to-use right there on the Visualizations pane. They are dynamic as well. And thank you for the trick on icon formatting, so cool! 🙂
Hi Hai Yen, Thanks so much for reading - glad to hear you found it helpful!
Pingback: How to Reference other Power Query queries