Overcoming the limitation of visual calculations

The biggest limitation of visual calculations is their reusability. Since they are created in a visual, their definition is not saved in the model. This is both an advantage and a disadvantage: your model does not get cluttered with a lot of one-off calculations that are specific to one visual in your report. On the other hand, if you do want to use the same calculation in another visual, you have to create it again, hereby increasing the risk of errors since you now have to maintain it in two places.

This problem can now be solved with the help of DAX user-defined functions, or UDFs for short. UDFs are reusable pieces of logic, written in DAX, that you can call in any place where you can evaluate DAX. So, combining visual calculations with UDFs opens a whole world of new possibilities with DAX and overcomes the limitation of reusability of visual calculations. Curious to see how that works? Well, read on, because I’ll explain all about it in this blog!

In our book, Microsoft Power BI Visual Calculations: Simplifying DAX (co-authored with Jeroen ter Heerdt and Marc Lelijveld), we have a lot of examples of what is possible with visual calculations. And of course, as it always works with projects, once it’s in production you discover that you could have done things differently, more elegant or simpler. So, I’ll use examples from our book, and immediately take the chance to simplify some of them as well.

Suppose you have a beautiful report with a lot of matrices (okay, so maybe not that beautiful) that show some different metrics over some different axes, like sales, ytd sales, costs, profit and quantity, over some combinations of years, quarters, months, brands, countries or product categories.

Now, because matrices are not the easiest visuals to ingest for a report user (one reason not to use them too often!) you can use a simple visual calculation to highlight to minimum and maximum value inside your matrix.

To do so for the matrix displaying sales per year and month, you can create the following visual calculation, that only provides a value of 1 for the minimum value and a value of 2 for the maximum value of sales:

MinMaxValue =

VAR MinValue = MINX(ROWS, [Sales])

VAR MaxValue = MAXX(ROWS, [Sales])

RETURN

SWITCH(

    [Sales],

    MinValue, 1,

    MaxValue, 2,

    BLANK()

)

By using the ROWS parameter as the table inside the MINX and MAXX functions, the minimum and maximum values are calculated over all rows, but for each column. That is to say, the minimum and maximum values over all months are calculated for each year.

You can hide the visual calculation in the visual and use conditional formatting rules to provide a nice icon to highlight the minimum and maximum values in the matrix, as shown in the figures below.

The resulting matrix then looks like this:

But what if you only want to highlight the minimum and maximum values inside the matrix as a whole? So not over all rows, for each column as we did now, but only the maximum and minimum of all the values inside the matrix? It’s a simple adaption of the axis parameter in the visual calculation, to change it from ROWS to ROWS COLUMNS, but if you have four or six matrices, this means changing it four or six times. That just won’t do.

The solution: DAX user-defined functions. Instead of writing the DAX expression to calculate the minimum and maximum values in a visual calculation, write it in a DAX user-defined function, or UDF, for short. The function is defined as follows:

FUNCTION fnMinMaxValue =

(pxTable : table expr, pxColumn : anyref expr) =>

VAR MinValue = MINX(pxTable, pxColumn)

VAR MaxValue = MAXX(pxTable, pxColumn)

RETURN

SWITCH(

    pxColumn,

    MinValue, 1,

    MaxValue, 2,

    BLANK()

)

It’s the same DAX as before, but now with two parameters to make it reusable. Instead of the fixed ROWS value, we have a table parameter pxTable with an expression passing mode. This means that the expression is evaluated every time the function is called. Furthermore, we have another parameter, called pxColumn, which will be a reference to the field for which we want to define the minimum and maximum values.

Instead of writing a DAX expression in a visual calculation, we can now just call the function fnMinMaxValue with the appropriate parameters to obtain the results we want to have. Create the following visual calculation:

fnMinMaxValue = fnMinMaxValue(ROWS, [Sales])

In a visual calculation, you are more than welcome to call a function! The DAX editor will not be very helpful here, it does not recognize the function you’re trying to call, so you’ll have to type it in completely yourself. But it does work!

In the visual matrix, it can be seen that the results are exactly the same as the direct visual calculation:

And now, you can easily play around with different values for the parameters. Use ROWS COLUMNS instead of ROWS to only show the minimum and maximum value of all values in the matrix. You can call the function in another matrix with a different metric as values by referring to that metric as the second parameter. All without the need to duplicate the DAX logic.

So, that was a bit of a silly example , because honestly, who builds reports with six matrices in them ;)?

But the general combination of visual calculations and UDFs is very powerful and can be used with great success everywhere in your report. As a more sophisticated example, consider the tornado chart we created in our visual calculation book:

In our book, we created no less than five visual calculations to create this chart. I realized this could be created simpler, with only one visual calculation (or you can make two visual calculations, to break it down a bit):

LeftBufferVC = ROUNDUP( MAXX(ROWS, [Sales Previous Year]), 0 ) * 1.2 - [Sales Previous Year]

But what if we store this logic in a UDF instead? Create the following function:

FUNCTION fnTornadoLeftBuffer =

( pxColumn : anyref expr ) =>

ROUNDUP( MAXX(ROWS, pxColumn), 0 ) * 1.2 - pxColumn

Note that it is possible to omit the table parameter in the function definition and use ROWS directly in the function definition. Again, the DAX editor does not understand what you mean exactly, it will create a red underline, but it does allow it and it works!

And we can now easily create a new tornado chart by following the steps below:

1. Create a stacked bar chart.
2. Put the desired field on the Y-axis, e.g. Continent.
3. Put the two measures you want to show on the X-axis, e.g. Costs and Sales.
4. Create a visual calculation that calls the fnTornadoLeftBuffer function with your top measure on the X-axis (that’s the one shown on the left), e.g. LeftBuffer = fnTornadoLeftBuffer([Costs])
5. Move this visual calculation to the top of the list of fields in the X-axis, and make the bar for this visual calculation 100% transparent.
6. Your tornado chart will be “upside-down” because the default sort axis order is now descending on the left buffer. Change the sort axis order to a descending order on you last metric, e.g. Sales.

You may want to remove some other titles, legends, et cetera to create a clean tornado chart. But then, this is the result:

Easy does it! It’s reusable, it’s elegant, and it’s accessible for any user that does not have a lot of experience with DAX. In my next book, I’ll will explore the combination of visual calculations and user-defined functions even further!