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.
Be the first to comment