Power Pivot Principles: The A to Z of DAX Functions – EVALUATEANDLOG

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at EVALUATEANDLOG. 

The EVALUATEANDLOG function

The EVALUATEANDLOG is a DAX function that evaluates a scalar or table expression returning the value and sending the same result to the DAX Evaluation Log event in JSON Format.  It has the following syntax:

It has three [3] arguments:

• expression: this is required and, represents the value of this expression that will be returned as well as sent, in JSON Format, to the DAX Evaluation Log event
• label: this is optional and, represents the Label column of the DAX Evaluation Log event as well as a part of the JSON payload
• maxrows: this is also optional and, represents the number of rows to return in case of a table expression.  The default value is ten [10].

Here are a few remarks for the EVALUATEANDLOG function:

• the DAX Evaluation Log is a trace event that can be captured by SQL Server Profiler or by the DAX Debug Output tool
• the EVALUATEANDLOG function is generally disabled in all other Power BI products, except for Power BI Desktop
• when the EVALUATEANDLOG function is disabled, it does not affect the environment; it just passes through the output of its child expression
• this method could prevent some DAX optimisations when the EVALUATEANDLOG function is enabled.  As a result, once you've finished debugging, remove this method from your DAX code
• the EVALUATEANDLOG function will have the red squiggly line (a technical term!) due to the current lack of IntelliSense support.

As an example, let’s define a measure, [Revenue]:

Imagine you wish to double-check whether the numbers you see on a given report are correct.  In the past, you would add two [2] measures, [SalesAmount] and [Cost], in your report to check the numbers.  Now you simply have to write

Your visual won't change since the measure will return the same result, but the modified expression will force the DAX Engine to deliver the intermediate results for Sum(Sales[SalesAmount]) and Sum(Sales[Cost]) to a hidden channel that I'll refer to as a "debug output" so you may examine them.  After you have finished inspecting and debugging, you may then remove these expressions as it might make Power BI run slower otherwise.

Come back next week for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.