Variables can simplify your DAX code, help with debugging and help improve performance. To use variables in your DAX measure you need to declare a variable using the VAR keyword, give the variable a name, and then assign an expression to the variable. You can use multiple variables in a measure but when using variables you must use a RETURN statement to specify the final output to be returned.
To show how to make use of variables I’ll go through a simple measure and rewrite it using variables, I’ll be using the Adventure Works Internet Sales tabular cube for this.
A measure called Total Sales YoY Growth % (shown below) has been created which calculates sales for current year and the previous year, then works out the percentage increase/decrease from the previous year to the current year.
You can see there is repeated logic for the CALCULATE block and this means it is evaluated twice, notice that SUM(‘Internet Sales'[Sales Amount]) is referenced 3 times. Using variables for the repeated logic gives the following code:
In this version the result of SUM(‘Internet Sales'[Sales Amount]) has been assigned to the variable TotalSales and the result of the CALCULATE section has been assigned to the variable TotalSalesPP. The CALCULATE section is now evaluated only once and assigned to a variable, this is a basic example so performance gain is insignificant but this method will help the performance of more complex measures.
To simplify the code further a new variable called TotalSalesVariance is created, and a final variable called Result is created to store the final calculation to be returned. The final code then becomes:
This version then allows you to debug the measure by using any of the variables in the RETURN statement, making it possible to verify the values for each of the variables:
The one thing to look out for with variables is filter context, once a variable has been declared and a value assigned to it you cannot use a CALCULATE statement to override the filter context and get a new value using the variable.
For example, TotalSalesPP = CALCULATE(TotalSales, PARALLELPERIOD(‘Date'[Date], -12, MONTH)) would return the same value as the variable TotalSales, hence TotalSalesPP = CALCULATE(SUM(‘Internet Sales'[Sales Amount]), PARALLELPERIOD(‘Date'[Date], -12, MONTH)) is the correct way to write this.
Hopefully this blog will help you introduce variables into your DAX code!
The UNICHAR() DAX function is a text function that takes a numerical Unicode value and displays its associated character. For example, UNICHAR(128515) will display as:
90% of the information the human brain processes is visual and we process images up to 60,000 times faster than text, so it makes perfect sense to use icons where possible to enhance reports. This scarcely used DAX function opens-up that option.
The below stacked column chart uses Unicode emoticons to enhance the readability of the ‘Genre’ axis labels.
So, how do we achieve this?
To produce this you will need to edit the query. In the ‘Data’ view, right click the relevant table and select “Edit Query”
First, duplicate the existing column you want Unicode characters for (genre in this case). Then use the ‘Replace Values’ option to substitute in the relevant Unicode numbers for each genre.
(this can be hidden from the report view as it contains nothing meaningful).
Next, create a second calculated column that uses a simple measure:
IconColumn = (UNICHAR(UnicodeNumberColumn))
This new ‘Icon’ column can now be used in reports the same way as any other text column.
Note how in the stacked column chart above, the original names have been included, this is good practice for two main reasons. One is clarity, a clown denotes comedy to most users, but could indicate horror to others, including the label removes the ambiguity.
The other reason is due to possible compatibility issues. It is worth pointing out here that the Unicode characters will only display when the character exists in the chosen font. In most cases this will be fine, especially for emoji characters, but just in case there are display issues it is worth including the full label.
Staying with the movie topic, the below chart shows movie ratings both numerically and visually created by a custom measure:
Stars = REPT(UNICHAR(11088), AVERAGE('IMDB 1000'[10 Star Rating]))
A measure that uses the UNICHAR() function will always be a text field and as such, normal formatting applies, in the example above we can set colours to be gold on a black background.
The previous examples do help readability but don’t really add anything meaningful to the report. The below table shows that the UNICHAR() function can add worthwhile content with customisable KPIs by combining it with conditional formatting.
There are 143,859 Unicode characters available, everything from emojis, symbols, shapes and braille patterns to dice and playing cards. Whether you want to offer further insight into your data, enhance the user experience or simply create something sublimely ridiculous, with so many icons at your fingertips, the possibilities are only limited by your imagination.
Further information on the UNICHAR() function can be found here: UNICHAR function (DAX) – DAX | Microsoft Docs
A list of Unicode characters and their respective numerical values can be found here: Huge List of Unicode Characters
Have you ever wanted to export a table from Power BI Desktop into Excel just to make sure the DAX you’ve written is performing as expected but ran into this error message: “Data exceeds the limit – Your data is too large. Some data sampling may occur”?
Most probably this has occurred because you’ve got more than 30,000 rows of data in your table that you’re trying to export. In the example shown below I’ve actually got 30,001 rows of data – a row containing column headers plus 30,000 rows of actual data.
If I now change the row count in my “Table To Export” table to 29,999 rows of actual data using a simple filter I have no issues exporting the data. This brings the grand total to 30,000 rows (29,999 rows of actual data plus a row containing columns headers).
But what if you really need to export more than 30,000 rows of data? Well there is a way…
Firstly make sure you’ve got DAX Studio installed on your machine.
Now within Power BI Desktop click “View” and then “Performance analyzer”.
This will open a new window pane called “Performance analyzer”.
Now click “Start Recording” and then click “Refresh Visuals”. Next within the Performance analyzer window locate the name of the visualisation which in my case is “Table To Export” and click the small “+” symbol next to it.
Next go ahead and grab the DAX query which has been used to construct the table. Click on the link “Copy query” which will copy the DAX query to your clipboard.
Next you’ll need to launch DAX Studio from within Power BI Desktop.
To do this on the top ribbon in Power BI Desktop click “External Tools” and then click “DAX Studio”.
Now within DAX Studio paste the copied query from your clipboard into the query window.
Next remove the variable which limits the number of rows which will be returned (TOPN) and replace the “EVALUATE” statement with “_DS0Core” as per the snip below.
Next change the output of the query to be executed from “Grid” to “File”.
Finally click the “Run” button and let DAX Studio Export your data containing at least 30,000 rows to a CSV file. Simple!
I hope this helps with any debugging you might need to perform in Power BI Desktop!
Being fairly new to DAX and having recently completed some DAX training I thought it would be good to briefly share my experiences of the courses I have completed. The two courses I have recently done are:
- Stairway to DAX and Power BI by Bill Pearson (https://www.sqlservercentral.com/stairways/stairway-to-dax-and-power-bi)
- Mastering DAX by Marco Russo and Alberto Ferrari (https://www.sqlbi.com/p/mastering-dax-video-course/)
Stairway to DAX is basically a series of posts, each covering one or two DAX functions, and is free to access. The Mastering Dax course is a paid video course covering various DAX functions and concepts, priced at $349.
The Stairway series was the first structured training material I did on DAX. Each post is detailed and contains a step-by-step guide on how to achieve the goal for that post, and there are practice sections to put the learning into context. Although the content and examples are based around PowerPivot it still covers the DAX concepts quite well, and new content is added regularly with the latest post on ALLSELECTED() added in October 2020.
I felt there were some good examples in the SUMX() section (Level 6), particularly when combined with RELATED(), I had not used SUMX() a great deal prior to the training so I found this section really useful. There were some sections that I felt were a bit long-winded and I found the Microsoft documentation on DAX (https://docs.microsoft.com/en-us/dax/dax-function-reference) covered the same content in a simpler way in my opinion.
The Mastering DAX video course is very well structured and has a good flow from simple concepts to more advanced techniques, with 21 modules at the time of writing. There is also an introductory course on the same website called Introducing DAX which is also a video course and is free to access, this is good as an intro into DAX before going into the more in-depth paid course so is definitely recommended.
What makes the video course even better is the exercises at the end of the modules, I found the exercises were a good way to get me thinking about what I had learnt and then apply that learning. There are some exercises where it is not obvious what the end goal is, and some exercises are a carbon copy from the video content so if you do the exercise straight after the videos then you are already likely to know the solution without much thought. More videos have been added recently where Alberto runs through the exercises and provides the solutions, this is really good if you don’t understand the exercise or need some guidance or an explanation on the solution.
I found both courses to be quite useful, although the video course suited my learning style better, especially with the exercises because I learn from doing rather than just reading. So, I feel got more out of the video course than the Stairway series and I still go back to it sometimes if I need some help with a DAX problem, along with the Microsoft documentation (link provided above) which is also a good reference point for DAX.
Based on your learning style you may learn more from one course or the other, so I’d recommend either depending on your learning style, or both if you want to mix things up. I’d also recommend supplementing these or any other courses with trying things yourself and just playing around with DAX, in my opinion the best way to learn is to just try it.
Next up for me is the video series on YouTube called Elements of DAX by Brian Grant!