From complex business intelligence to data modelling and analytics scenarios, Data Analysis Expressions (DAX) can help you solve some curly problems with just a few clicks of a button in Power BI. Here at Sensei, we think being able to write DAX queries and achieve your desired output in a wide range of real-world scenarios is pretty invaluable. So, in this blog post we're going to show you our four favourite DAX tricks that we've used to solve business problems during Power BI implementations for our customers.
For the purpose of this blog post, we'll be assuming that you have used DAX before or know its application.
DAX in Power BI
DAX is a short form for Data Analysis Expressions which can be used in formulas or expressions to manipulate the data within a Power BI model. DAX can be used to create new measures, calculate columns or manipulate data within existing tables using built-in functions. Some DAX functions are identical to Excel worksheet functions, but DAX provides much more than Excel when it comes to slicing and dicing data.
1. Dynamic Titles
Creating dynamic titles for charts or visuals is a popular request from end users and managers when designing reports and dashboards. While there is no out of the box feature in Power BI yet to achieve this, keep reading to find out how we used DAX functions to make it happen.
Consider the following chart in Power BI showing value by state. By default, its title is static and cannot be changed based on user selections such as selecting an individual column to highlight its specific value.
Now let’s see how using the following DAX functions we can create a dynamic title for this chart so that when a selection is made on the chart, it reflects that in the title and will also work if there are multiple selections made.
ISFILTERED – This function is a true or false check on a column i.e. it will return True if filter is applied and False if not. We use this function to check if a selection is made on the visual.
VALUES – This function returns a one-column table that contains the distinct values from the specified column. We use this to identify values for the selection made on the visual.
CONCATENATEX – This function concatenates the result by delimiter i.e. in our case it will put together the result of selection made on visual by comma-delimited string.
So finally, we will create a new DAX measure in Power BI, as seen below.
Once this measure is created, the trick is to turn off the title option for the visual by going to the Format tab and then creating a new text box, pasting in the new measure created as the value and placing it above the visual in such a way that it appears as a title for that visual. Then when a user makes selection on the visual, the measure created will be filtered making it appear as a dynamic title for that visual.
2. TopN or BottomN
Sometimes the visual will have a large number of values to show, and that may create an unappealing aesthetic within the report. In that case, itmakes perfect sense to limit data points for that visual to show only top or bottom values such as ‘Top 5 Projects’ or ‘Bottom 5 Risks’. Up until October 2016, Power BI did not have an out of the box feature to do this, and so a DAX trick was a widely popular alternative. Having spent a lot of time helping our clients and meeting their requirements using this trick, we thought we would showcase it here, as the usefulness of it cannot be undervalued.
Consider the following column chart in Power BI that shows average fuel prices, both by different service stations and by fuel type.
While the information shown is very useful, the overall presentation looks crammed and it’s hard to distinguish values between each data point. In this case, a useful trick would be to limit the number of data points shown on the visual by displaying only top n number of values along the x-axis, making it more appealing to the user viewing it. To achieve this, we will make use of the RANKX function of DAX.
RANKX – RANKX functions ranks a number in a list according to its value. So, for example if we had project name and the cost of project against that in a table it will provide ranking of the project names based on the cost depending on the sort order we’ve defined.
Note: RANKX is a complex function with varied applications and so you may not always achieve your desired outcome. For example, RANKX may provide different output when used for a column that is sorted differently.
The first step is to create a measure that is going to total the price across all data points. Then using the RANKX function we rank each store based on their price comparing it with the total of all, which comes from the measure we created earlier.
Result of this will be as below when viewed as a table.
Finally, in the column chart visual, apply the measure created via RANKX function as a visual-level filter, and filter it to show values less than or equal to 5. This will update the visual to only show average of price by top 5 stations. In a similar fashion, Bottom N records can be shown just by changing the sort order in the RANKX function and ranking the stations by lowest of the price to highest.
3. Time Based Comparison
One of the most well known DAX techniques is time based comparison. Our all-time most requested task from our clients is to provide a way to compare project costs between two time periods in the same visual. DAX is capable of complex time based analysis, however, for this example we’ve kept things simple.
Consider we have the following visuals showing cost by projects and date. To do a cost comparison of this year vs last year we will make use of following DAX functions:
CALCULATE – As the name suggests, CALCULATE allows you to perform calculations on the subset of data that is generated by filter argument that it requires.
SAMEPERIODLASTYEAR – Is a built-in function that provides column of dates one year back from the supplied list of dates.
The first step of the process is to create a measure that is going to calculate the sum of the total project costs. This can be created as below:
Then we need to create a new measure that will calculate the total cost, but for the same period last year.
Finally, plotting those two measures on a visual against the date means that project cost can be tracked for this year vs. the previous year.
4. DAX Measure Management
While this may not qualify as a DAX trick, we definitely think it’s something worth knowing, and hopefully you can implement in your future Power BI developments. It is not very hard to end up with a complex Power BI data model when building your reports. So, if your Power BI data model looks anywhere close to the one shown below and contains a long list of DAX measures, then read on.
Once created it becomes hard to keep track of all the measures in place across all the separate tables. So, a better way would be to arrange them all in one place so when you’re searching for a specific one, there is only one table to look for. To achieve this, first create a table via the Enter Data option on the Home tab. Give a meaningful name to the table such as Custom Measures and click Load.
Then, highlight DAX measures created, under Modelling tab click the Home Table dropdown and select the Custom Measure table. Follow these steps for each measure created across different tables and you will end up with all the DAX measures in one easily locatable and manageable place.