If you had a chance to read my first blog on “How to Speak Power BI”, hopefully you are feeling better about the basic terminology of Microsoft Power BI and how the software platform is actually made of three different elements: Power BI Desktop, Power BI Service and Power BI Mobile. Now let us dig a little deeper into the language of Power BI.
There is always confusion around slicers, filters, measures, columns, drilldowns, and drillthroughs. If you are the client, it can sometimes be difficult for your Power BI expert to understand what you are asking for and how you would like to see something accomplished with Power BI. At the same time, it can be as equally confusing for the Power BI expert to translate or decipher what you are asking for them to design. It’s no one’s fault, it is just one of those areas that can be a bit tricky. After reading my series of blog posts on “How to Speak Power BI,” I hope that you can be more confident and less confused when the topic of data visualization and Power BI comes up.
Nailing Down the Terminology
Many of the intermediate vocabulary words I’m going to address lend themselves to being discussed as pairs. This is especially true because of the level of confusion around these pairs when talking about Power BI and data visualization.
The First Pair: Columns and Measures
The first pair I’m covering is columns and measures. (This has given a little Power BI consultant many o’ headaches… And that glass of wine hasn’t helped me yet so I’m hoping this blog post will!) First up is trying to make it easier for you to understand the differences between a column and a measure in Power BI. Both are important and can sometimes be used interchangeably, but there are some definite tradeoffs between them. Here goes the explanation.
- Columns and the results for each row are going to be saved in your data model. This means that your .PBIX file will be larger, and it could have an effect on the performance of your report and dashboards, and how long it takes to load your data. But it’s not all storm clouds and tornadoes, using a column also makes it possible for you control some of the formatting of data in your visualizations. By making use of a conditional column, you can determine the order of categories or labels of your data. Here’s a simple example of using a conditional column and then sorting it properly: so, your data has some date fields including a column for the month, but it is numeric, and you want it to be the name of the month. You would use a conditional column where 1 = January, 2 = February, and so on (FYI the conditional column takes a little more than this but not much more, and will throw this blog post off course). Then on your report page, you can use a slicer to give your audience the ability to pick any month of the year and see that selection’s data. But wait, you can only sort your column numerically or alphabetically so that leaves your report view confused because April is first in your slicer. You can go to the modeling table and set the MonthName column to sort by the original column so that January will be first in the list.
- Measures are calculations that are not saved in your data model. They are evaluated when you use it in your visual and are best for aggregations. Aggregations are sum, average, mean, percent difference, etc. Power BI will denote measures with a little calculator icon beside them on the right-side Field pane of the software. Measures can be especially helpful in that you can use data fields from related tables in one calculation. Microsoft has made measures even easier to use by creating Quick Measures. Quick Measures are a “drag-and-drop” method to build popular calculations without having to know enough DAX to write them yourself. Quick Measures include aggregations, filtering, time intelligence, running totals, and basic mathematical operations. Running totals can be very useful for sales staff. Year-over-Year percent difference can help managers make sure their teams are making their goals. And even if you are solid with writing DAX expressions, Quick Measures are such a simple and quick way to build calculations.
The Second Pair: Filters and Slicers
Next is understanding filters and slicers. This doesn’t cause the headache that columns/measures do, but it can be confusing when discussing design and use of Power BI reports. So here it goes.
- Just to be on the same page, please know that a slicer IS A FILTER. A slicer is a visual that you have on a page of your report. Slicers are typically used for date, time, and categories. You can also have multiple on your report page but remember that slicers only affect the data on the page that they are on (unless you sync slicers but that is for another time and another post). So when your Power BI expert (maybe me!) is asking if you are happy with the slicers in your report, we are only referring to the slicer visuals used on Power BI report pages.
- Filters! Filtering is what got my little Kansas-raised Mac-loving heart to pitter-patter Photo-SlicerVsFilterJPGfor Microsoft Power BI. To this day, I’ll happily load a decent sized Excel spreadsheet into Power BI just for the filtering capabilities and slicers even though I can do the same in Excel. Filters can be applied at the data load point, in the Query Editor to speed up data loading and refreshing, and on the Visualizations pane when designing Power BI reports. The Visualizations pane will give you the option to apply filters at each level of your report: visual, page, drillthrough, and report, and will likely be where you use filters the most. If you put a filter at the report level to only see your May 2017 data, this will be applied across your entire report and will overrule any slicers on your pages. Filters on the Visualizations pane will give you the options ranging from basic to advanced and relative depending on the type of data you are using for the filter. Filters and slicers provide a powerful means to making your data more useful and to work for you instead of you working for it.
The Third Pair: Drillthrough and Drilling
The third and final pair I’ll talk about in this post will cover what gives me the most pain, but only when talking about Power BI with people who use or have used other data visualization and business intelligence software (like Tableau, FusionCharts, and Qlik). Drillthrough and Drilling (drill up or drill down) can bring the most confusion into the conversation.
- Drillthrough means having a page in your report that provides details on a specific entity based on a column of data. It could be geographical, client companies, products, departments, offices, or individuals. The options are nearly limitless. How it works is that you set up a page in your Power BI report that shows details on that entity through visuals so that if you right-click on other pages in the report showing that category of data, it will automatically filter to that specific product or client on the drillthrough page. When you create this drillthrough page, Power BI will automatically create a back button on the page. Plus, a useful little tip, you can hide the tab for your drillthrough page so that once it is published to Power BI service, it can only be reached by right-clicking and selecting it. More info on setting up a drillthrough page can be found here.
- Drill down is on the visual level. Not all, but many Power BI visuals allow for the option to have drilling. Drill down and drill up can provide the point of most confusion when discussing data visualization with a Tableau or other software user. Often but not always, when they are saying that they “want to drill down in the data,” they are referring to drillthrough in Power BI. I’ve experienced my share of confusion and frustration over this very issue with terminology. I’m not saying one is right and the other wrong, I’m just trying to advocate for understanding and using the software company’s official terminology when discussing that software platform. Drill down is most often used with date/time intelligence but can be used with any hierarchy or ‘stacked data.’ By stacked data, I am having your data drill down from region to city to store, or office to department to staff. Power BI also gives you the option for drilling down to the next level or expand all down one level. Microsoft’s documentation for Power BI is greatly improving so you can go here for visual and video explanation of the drill down concept.
More Vocabulary Words?
There are still so many Power BI vocabulary words I would like to cover, but I don’t want to overload anyone, including myself, so go have a cocktail and let what you just read seep into the nooks and crannies of your brain! I hope the above will help you better speak Power BI and better understand some of the pieces of the software.
If you have questions and comments, reach out to me on LinkedIn or Twitter @katecoriell and I’ll hopefully be able to provide some guidance. Keep an eye out for the advanced level(s) for How to Speak Power BI!
Need help with your data analytics project? Contact us online or give us a call at 240-406-9960.