Ask Learn
Preview
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign inThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
In the first PivotDiagram tips post, I talked about some of the alternate ways to create a new PivotDiagram. In this post, I’ll cover an alternate way to filter your data.
After creating your diagram and hooking up your data source, the most frequent first step we see is filtering down the data set to include just the information you want. It is possible to filter by using a dialog (drop down any of the menus in the PivotDiagram window, then click on “Configure Column”) but I find that to be a bit of a pain. It is much easier to just use “Add Categories” to set up a grouping that includes the data you want, then select the shape you want and use the “Promote” command to make that shape the root shape. This effectively filters the data quickly and easily.
For an example, I’ll connect up to a data source containing some simple animal data and filter the data down to only the horses. This is the data I’ll use (I've also attached it to this post):
Here’s the step by step:
Following the above steps does a simple filter to one value in one column in the original data source.
You can also filter by multiple columns by adding multiple categories to the PivotDiagram, then selecting and promoting a single shape on a lower level. If you are familiar with SQL or another query language, think of this as creating an AND filter for the values that shape represents. In the diagram below, if you promote the highlighted shape you’ll have a filter that is “WHERE Animal=’Horse’ AND Gender=’Male’”.
Sometimes you also want to be able to allow two or more values from a single column in your filter. You can do this as well, by using the “Merge” command before you “Promote”. “Merge” is a great feature by itself (I’ll cover it in more detail in a later post) but it is particularly useful when doing this type of filtering. Using the same example, now we’ll filter the data down to include both cats and dogs.
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign in