1. Performance tips in Power BI
Welcome back! In this final demo, we'll use the performance analyzer again, and look at how bi-directional filtering can be avoided to improve performance.
Recall that you created a GEO_ID column in a previous exercise. It was done by merging three separate columns into one using Power Query. Alternatively to merging, you could create a new column, let's say 'concat', and use the CONCATENATE() function to concatenate for example NAICS code and establishment age code. Notice that CONCATENATE() only allows two arguments. To concatenate three columns or more, you'll need to use concatenate again, nested in the first CONCATENATE() call. In this example, I concatenated NAICS code, establishment age code, and geographic area name, which looks like this in a table.
In the View menu, you can open the Performance analyzer. After starting the recording, refresh the visuals a couple of times to measure the duration in milliseconds it takes to create the report. You'll need this in the exercises, where you'll compare the performance of the CONCATENATE() function with merging columns in Power Query.
A final action to improve performance in Power BI could be when you are working with lots of bi-directional cross filters in the data model. In the example where you had a report with a slicer for year and establishment age code, you don't want users to select a year or code that isn't present in the connected fact table. We achieved this by adding a bi-directional cross filter in the data model. However, adding more and more bi-directional cross filters comes at a performance cost.
An alternative for bi-directional cross-filtering is by using DAX syntax. First, let's remove the existing bi-directional cross filters. Create a new measure, let's call it slicer_filter, which will check whether the fact table, business establishments by age, is not empty. If so, this formula returns the integer value of true which is one, and zero when it is false. You could then use this measure as a filter on the Year slicer. As a result, the Year slicer will only show values which exist in the fact table, and will in this case get rid of all the 1960s data points.
Note that you can't place a measure as a filter on the whole page or all pages levels. So to apply the slicer_filter on the establishment age code slicer as well, the filter needs to be applied in the same way.
Don't worry too much about the precise formula, you'll get the chance to practice this during the exercises.
The end result is that this allows you to do the equivalent of cross filtering without all the performance penalties and potential data issues related to bi-directional cross filtering.
Let's get to the final set of exercises and improve Power BI's performance!
2. Let's practice!