WeвЂ™ve certainly got more options than last time! Choose both fields from the вЂњMaterialsCountвЂќ table:ДЄnd look at thatвЂ¦ we can now slice by the total number materials in each product! Click in the middle of your Pivot and choose to insert a slicer. Jump into the Manage window, and set up the relationships between the model fields of both tables:ДЄnd thatвЂ™s really all we need to do here. We now need to head into Power Pivot to link this new table into the Data Model structure. Only create the connection and load it to the Data Model.The final step we need to do in Power Query is load this to the data model, so letвЂ™s do that now: Very cool! WeвЂ™ve now got a nice count of the number of distinct materials that are used in the production of each door. Set up the Group By window to count distinct rows as follows:.Now, letвЂ™s get that Distinct Count weвЂ™re looking for: Those wonвЂ™t help, so letвЂ™s get rid of them: WeвЂ™ve not got a nicely ordered list, but thereвЂ™s a few duplicates in it. Sort the material column in ascending order.Sort the model column in ascending order.Leave the default name of вЂњCustomвЂќ and use the following formula: &.Now we need to narrow this down to a list of unique material/model combinations, then count them: On the right side, rename it to MaterialsCount.First thing, we need to make sure itвЂ™s got a decent nameвЂ¦ LetвЂ™s modify this puppy and get it into the format that will serve us. This will let us preserve our existing table in the Power Pivot data model, but reshape this table into the format that we need. YouвЂ™ve now got a new query that is referring to your original. Right click your MaterialsList query and choose вЂњReferenceвЂќ.When you expand that arrow, youвЂ™ll see your existing query there!.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:.In the Workbook Queries pane, right click the existing вЂњMaterialsListвЂќ query and choose Edit.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! 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. LetвЂ™s see if we can use Power Query to help us with this issue. IвЂ™ve wanted this ability a lot, but itвЂ™s just not there. Okay, itвЂ™s not surprising, but it is frustrating. We want to slice by the DistinctMaterials measure that we createdвЂ¦ except. Now, click in the middle of your Pivot, and choose to insert a slicer. 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. Modify it to ensure that it is set up as follows: The secret to the formatting is to select the values and set up an icon set. Add a little conditional formatting to the PivotTable if you want it to look like this:.DistinctMaterials:= DISTINCTCOUNT(MaterialsList).Create a DAX measure to return the distinct count of materials:.Create a PivotTable with models on rows and material on columns.Use Power Query to pull the MaterialsList table (shown below) into Power Pivot.The first question is a classic Power Pivot question. 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. They have a few different models of doors, each of which uses different materials in their production. The scenario weвЂ™re looking at is a door manufacturer. (For Excel 2010 users, you have to run your queries through the worksheet and into Power Pivot as a linked table.) Background IвЂ™m not saying this is the only way, by any means, but itвЂ™s an approach that I find works for me. This gives me the ability to re-shape my data and load it into the data model the way I need it.

My approach to solve this issue is to use Power Query to load my tables. In other words, theyвЂ™d built the DAX required to generate their output, and wanted to use those values in their slicers. The issue was that the individual had built a model, and wanted to add slicers for value fields. Earlier this week I received an email asking for help with a Power Pivot model.

Excel data slicer