Power BI Blog: EVALUATEANDLOG
27 April 2023
Welcome back to this week’s edition of the Power BI blog series. This week, we look at a new DAX function, EVALUATEANDLOG.
If you have ever wanted to see the intermediate results of a DAX expression or to debug your DAX by printing out values, then consider the new DAX function EVALUATEANDLOG. It takes any DAX expression, evaluates it and returns the result. That’s not all though: it will also log the result (hence the name EVALUATEANDLOG) to the DAX evaluation log that you may read out using tools like SQL Profiler. It’s very much like a “print debugging statement”.
For example, consider the following measure that calculates the sales growth year over year, using a Sales Amount measure and the SAMEPERIODLASTYEAR function:
YoYGrowth := [Sales Amount] – CALCULATE( [Sales Amount], SAMEPERIODLASTYEAR(’Date’[Date]))
To make sure this measure does what you need it to do, you can now wrap the parts of the measure in EVALUATEANDLOG to see the intermediate result, as in the example below. All the while, Profit will still return the same result as before:
YoYGrowth := EVALUATEANDLOG([Sales Amount]) – EVALUATEANDLOG(CALCULATE( [Sales Amount], SAMEPERIODLASTYEAR(‘Date’[Date])))
However, additionally, the DAX evaluation log will now contain the result of Sales Amount and CALCULATE( [Sales Amount], SAMEPERIODLASTYEAR(’Date’[Date])).
You can see the output using SQL Profiler by connecting to Desktop, starting a trace and subscribing to the ‘DAX Evaluation Log’ event. Alternatively, you can use the open source DAXDebugOutput tool. Please note that the DAXDebugOutput tool is not an official Microsoft tool and hence is neither signed nor supported.
In the meantime, please remember we offer training in Power BI which you can find out more about here. If you wish to catch up on past articles, you can find all of our past Power BI blogs here.