DAX CALCULATE Debugger

CALCULATE is the most powerful function in DAX, as it allows you to change the filter context under which its expression is evaluated to your hearts content. But with big number of options to choose from, often comes big frustration when the results don’t match expectations. Often this is because your syntax to modify the filter context doesn’t do what you’ve intended. Unfortunately CALCULATE only displays its result and not how it achieved it, so debugging becomes a challenge. This is where my CALCULATE Debugger measure can help out:

DAX CALCULATE Debugger

This is a measure that returns a text-value, showing the number of rows of the adjusted filter context table, the MIN and MAX value of the selected column as well as up to the first 10 values. Just place this measure beneath the CALCULATE-measure in question and try to find the error 😉

Just have in mind, that this only works for standalone CALCULATE-functions and not for those who are nested in other functions (who modify the evaluation context).

The YTD-measure is defined as follows:

YTD = CALCULATE ( [Amount], DATESYTD ( 'DimDate'[Datum] )

The code for the DAX Debugger measure looks like this:

In row 2 you fill in the filter expression from the YTD-expression (2nd argument: ‘DimDate'[Datum]). You can choose from which column the values shall be shown, just write that in rows 6, 7 and 11 ([Datum]). If you want to adjust the TOPN-figure for the sample values to be shown, replace the 10 in row 9 accordingly. If you don’t want to show sample values at all, just uncomment row 13 and comment out row 14 and 15.

Thanks to Tom Martens for providing the crucial hint of how to reference a column from a table that’s defined in a variable by using X-functions!

Further adjustments have to be made, if your filter expression uses the syntax sugar of boolean expressions like so:

CALCULATE ( [Amount], Product[Color] = "Red" )

This expression only returns a table when used as a filter argument in CALCULATE but not standalone in a DAX variable. So you’d have to translate the filter expression to the native underlying code like so:

FILTER ( ALL ( Product[Color] ), Product[Color] = "Red" )

As this cries for some automation, I’ve produced some nifty M-function that does all that autoMagically. It lives in my M-function-library so I have it at hand within PowerBI for immediate use.

The M-function

This function creates the DAX-code in the query editor. Just fill in the parameters (see below) and the DAX code will be created automatically: Just copy and paste as a new measure.

How to fill the parameters:

  1. filterExpression: DAX-code of the CALCULATE filter expression
  2. myColumName. Name of the column whose values to show
  3. MaxFilter: This is a optional parameter: Fill in a different number from 10 if you want to change the default value for the TOPN selection of the sample values to be shown.

This function detects boolean expressions automatically and produces the appropriate code.

If you don’t know how to use M-function-code, please check out Ruth Pozuelo’s video.

Enjoy & stay queryious 😉

The full Table.ContainsAnywhere function for Power Query in Power BI and Excel

In a previous post I introduced the concept of a function that searches for an occurrence of a character or string within all columns of a table. Here I share the full “Table.ContainsAnywhere” – function with parameters for many useful options.

Function parameters and options

  1.  The first parameter “MyTable” refers to the table to search through
  2.  The 2nd parameter “MySearchStrings” can be either a text field or a list of strings to be searched for. The function will take care of any of these cases automatically.
  3.  If the 2nd parameter is a list and this 3rd parameter is null or not speified, the function will return true if any of the list items is found within the table. But if set to “All”, all list items have to be found somewhere in the table for the function to return true.
  4.  By default, the search will be made in a case sensitive mode (as this is the default-mode in Power Query). But any entry into the 4th function parameter will turn this to a case insensitive mode instead.
  5.  By default, the string or list entry has to match fully with any entry in the table. Again, any entry in the 5th parameter swaps that to a partial match.

Function code

I encourage friends of the M-language to read through the documented code of the “Table.ContainsAnywhere”-function. It shows a fairly compact way to handle the 24 different functions that are needed for all possible function parameter combinations. For each parameter, I created one function module that covers the part of the function-logic that is specific to this parameter. These function modules also carry the case selection already. So they will deliver just what’s needed to the main query part (2), where they can then be executed sequentially. This way I avoid heavy branching with if-then-else-statements and redundant code.

Enjoy and stay queryious 😉

Bug in Power BI R Scripts “package … was installed with different internals”

Today I spent many hours hunting an R-script error in Power BI and before Steph Locke came up with a solution for this, I came across a couple of posts and heard of other people, having the same problem. This blogpost is to make distribution of the solution a bit easier and to hopefully to help other folks with the same problem in the future.

The Problem

When running R-scripts in Power BI, I got all sorts of error-messages who all had one thing in common: They were complaining about one or more packages being installed by an R version with different internals.

They ran without any problem in RStudio or on other machines, just not on my own specific laptop.

The Solution

I have no idea what causes the problem, but Steph Locke showed me how she solved it before. She installed the problematic packages into R’s program-folder and pointed to this folder in a parameter, when using the function.

Step-by-step-instruction

1) Find your paths

Display your R-paths by using this function: .libPath()

The first path is the one which Power BI most likely will reference by default for the package information and the one which RStudio uses to install the packages to. The second path in the program folder belongs to a folder that also contains the R-program itself.

Now to solve the problem you have to install the packages that turn up in the error-messages into this second library folder.

2) Install package into the program folder

Therefore you have to open the RGui with admin-rights. If you don’t have a symbol for it on your desktop, you’ll find the file in the bin-folder:

install.packages("scales",lib="C:\\Program Files\\Microsoft\\R Open\\R-3.5.2\\library")

The fist function-parameter takes the name of the problematic package and in the second parameter you have to pass in the path to the library folder within R’s program folder. That’s the 2nd folder from the step above. Make sure to turn the slashes. The double-slashes might not be necessary for everyone, but for me it wouldn’t work otherwise.

3) Adjust R-script

In this last step, you have to add one or more lines of code on top of your existing code:

library("YourPackageName", lib = .libPaths()[-1] )

This formula will load the package, and the 2nd parameter will determine the path from which the file will be taken. Here, the first item from the paths from step 1 will be skipped, so the library in the program folder will be chosen instead.

Just install one package, run the script again, see if another package pops up – rinse – repeat – until you’re done 😉

Please vote for the bugfix here: https://community.powerbi.com/t5/Issues/R-visual-error-quot-package-was-installed-by-an-R-version-with/idi-p/512759

& stay queryious 😉

A new Table.ContainsAnywhere function for Power Query in Power BI and Excel

The native Table.Contains-function in Power Query tells you if one or more strings are included in one or more of its columns. But you have to be specific about which strings you search in which column. But what to do if you want to search a string in all of its columns instead? Use my new Table.ContainsAnywhere function.

Problem

In the native function, you have to pass in a record with search term and column name. So if you search for “blue” in column “Description”, your formula would look like so:

Table.Contains( YourTableName, [Description = "blue"] )

But that’s not what I want in this case. I want the formula to search through all columns within the table for the occurrence of “blue”.

Solution

One way would be to transform the list of column names of the table to a nested list where for each column name, the search-string would be added. But that gets a bit clumsy if you want to use it in a Table.AddColumn-step. So I’m going a different path instead:

Say this is my table and I want to know if the string “INCOME STATEMENT” is included in any of its fields:

Table to search all columns for a specific string

 

1. Split the table into a list of lists where each list contains all fields from one row:

Table.ToRows(Source)

Table.ToRows creates one list per row in a nested list

2. Combine that list into one, means you have all fields of the table in one big list:

List.Combine(Table.ToRows(Source))

Combine list of nested list into one (expanded) list

3. Check if this list contains the search term:

List.Contains(List.Combine(Table.ToRows(Source)), "INCOME STATEMENT")

Use cases

I’m using it to catch some specific tables from SEC-filings for example. This is the result of a Pdf.Tables-function to extract quarterly report data from a large pdf file. It shows all the different page- and table elements in it:

Table.ContainsAnywhere function in action

I’ve added a column with the function above and can now filter on “true” to extract the matching tables.

Variations

You want an case-insenstive match? Or search for multiple strings? And be able to distinguish between any and all-matches? Or even go for partial matches?

Then watch out for the next article where you get the function with all bells and whistles.

Enjoy & stay queryious 😉

How to cancel your Power Query refreshes fast in Power BI and Excel

If you’re working with large data or complex queries that take a long time refresh, cancelling one of those refreshes can even take longer time, especially, if the query has run for quite some time already.

Luckily, there is an easy trick to cancel refresh without loosing the work you’ve done already:

Step-by-step-instruction

  1. Open the task manager (Ctrl + Alt + Del -> select Task Manager or rightclick the NavBar )
  2. Select (one of) the “Microsoft Mashup Evaluation Containers” with a high CPU-usage (below the main process!)

    Cancel refresh by ending a Container-task

  3. Right-click mouse and choose “End task”.
  4. A pop-up-message like this will appear – just click cancel (and don’t click “Report this issue” !!):

    Cancel refresh, but don’t report an issue

  5. Another scary message might appear as well – just click close:
  6. There might also be a message that other refreshes are currently running and if you want to cancel them as well – choose cancel.

That’s all: All the changes you’ve made in the query editor and forgot to save before refreshing will be kept and you can continue your work from here.

Enjoy & stay queryious 😉

Easy way to retrieve Teams data in Power BI via Flow (and other data from the Graph)

In a previous post I’ve described how to use a custom connector to retrieve data from the Microsoft Graph API. But this requires to register an App and adjusting the M-code in the connector itself requires some M-knowledge. So I thought it might be a good idea to share an alternative method to retrieve data from Teams for example, that works out-of-the-box. There are a couple of endpoints supported currently:

Also, these triggers are available:

I’m using the “Get messages” to retrieve all messages from a teams channel.

High level overview

Fetch the data with a standard-connector from Flow and save it to a JSON-file on your drive. From there you import it via a standard JSON-connector into Power BI.

In Flow

I started my flow with schedule trigger that refreshes it daily at midnight. Then get messages from Teams by selecting the Team- and Channel-name. You will have access to all the teams that you’re part of.

Please regard that this connector is in preview at the time of writing, so could still change. What I hope is, that the connector will be extended to include filters (for dates for example) as well, so one doesn’t have to pull all conversations with each call.

That step returns a JSON that I want so save into a file as it is, as it’s so much easier to extract the data from a nested JSON like this in Power Query than in Flow. Therefore you can add a compose step or transfer the returned body directly into the Create file-step with a little trick.

1 Compose

2 Directly

In the field “File content” you choose the “Expressions”-tab from the dynamics expressions. There you just type a space –> click on tab Dynamic Content –> choose “Body”. That will create the following formula that you simply accept: body(‘Get_messages’) (of course, you could also quickly type that in manually if you want to avoid this slightly fiddly clicking.

Back in Power BI, I import it as a JSON and simply expand all the columns I need and filter as desired.

Enjoy & stay queryious 😉