Random Procrastination

Darren Gosbell [MVP]

Category: DAX Studio

DAX Studio v2.13 released

The next version of DAX Studio has just been released and you can get it from the home page at https://daxstudio.org

This release has actually ended up to be a bit larger than originally planned, but hopefully this means there will be something in here for everyone.

In addition to a number of bug fixes and smaller improvements, this version includes the following major features:

  • Resilient Connections
  • Native Excel output
  • Clipboard results target
  • New Options layout
  • View Metrics improvements
  • Query Builder improvements
  • Prompt before refreshing metadata
  • Export Data improvements

Read on for more detail about each of these.

New Features

Resilient Connections

This is one of the bigger features that hopefully no one will notice. I spent a few days re-architecting the internals of the connection management so that now every tab has it’s own ConnectionManager object and if a connection is broken for any reason the ConnectionManager will automatically attempt to re-connect to the same data source and database. This is particularly an issue for Azure Analysis Services and the XMLA endpoint for Power BI Premium where backend operations like deployments, scaling and model synch operations can cause sessions to be dropped. Whenever the connection is retried for any reason you will see a warning in the Output pane like the following.

If you see one of these warnings while you are doing anything involving performance tuning you should re-try the previous operation because at least part of the time would have been taken up by re-connecting to the data source.

Native Excel Output

As of v2.13 you can now output directly to a static XLSX file from the standalone version of DAX Studio (so no need to launch from the Excel add-in just to output data directly to an XLSX file). The existing csv export format was specifically optimized to work well in Excel, but I have heard that in the past people were sometimes using the Excel addin just to get output into a native Excel file. In addition this should be much more efficient on memory usage as the data gets streamed directly to the file and you do not need have to have Excel running.

To keep in synch with the Excel addin I have also added a “Linked” Excel output. This basically generates an ODC file with an embedded DAX query.

I had previously held off from implementing a native export to Excel as there was no good solution that did not involve generating the Excel file in memory before saving it to disk. But thanks to a new library called LargeXlsx it’s now possible for me to generate potentially huge files while maintaining a low memory footprint.

Although note that Excel files still cannot exceed 1 millions rows. That limitation is built-in to the file format.

Clipboard Output Target

In addition to the new Excel result targets we now also have a Clipboard target. When using this the results of any queries are sent directly to the Windows clipboard where you can paste them into another application.

New Options layout

Over the years I’ve added numerous settings to the options page in DAX Studio, but it’s now at the point where having everything visible on the one page does not work and it’s just too hard to find things. So I’ve re-built the Options page using a new framework that organises the settings into categories which can easily be browsed through and searched

Options are grouped by categories
You can now search for options

View Metrics Improvements

There have been a number of small improvements to the View Metrics functionality in this release including:

  • Added a Loading indicator. There was one in the status bar, but the Metrics view has been aligned to show the same sort of “busy” overlay as the other windows do.
  • Fixed Sorting in View Metrics table view to do a nested sort and to sort Descending on first click
  • View Metrics – Fixed KB/MB/GB (it was lowercase, which is for bits not bytes)
  • Added Tooltips to most of the columns

Query Builder Improvements

The original filter patterns used by the query builder were copied from queries generated by Power BI Desktop and were in the form of

FILTER( KEEFILTERS( VALUES( <column> ), <condition> ))

this has been improved to produce more efficient queries with the following pattern

KEEPFILTERS( FILTER ( ALL( <column> ), <condition> ))

The Query Builder now also supports BETWEEN filters for dates and numeric columns and IN and NOT IN lists for text columns.

Prompt before refreshing

There is now an additional option in the automatic metadata refresh where you can ask DAX Studio to prompt before refreshing.

Export Improvements

The Data Export feature will now check if the data source supports the TOPNSKIP function and will use that if possible to export data in batches of 1 million rows at a time. This should allow us to export massive data volumes without triggering out of memory errors. I was able to do a test exporting 120 million rows from Power BI Desktop which generated a 20 GB csv file on my laptop (with 16 GB of RAM) and during the export Power BI stayed at around 1 GB of memory usage and DAX Studio hovered around 300 MB of memory usage.

Miscellaneous

The default file export format has been changed to csv instead of tab delimited since csv is probably a more common format and handles things like embedded line breaks better.

Fixes

  • Fixed #400 duplicate New option in customize Quick Access Toolbar
  • Fixed #401 unable to re-show Power BI Performance window after hiding it
  • Fixed #405 / #419 incorrectly trying to write external tools file when doing a non-admin install
  • Fixed crashes when right-clicking on certain tool windows
  • Fixed #411 Export to csv not quoting strings
  • Fixed #404 improved code completion interactions

DAX Studio and Tabular Editor – better together

I love love the way Phil Seamark likes to push the art of the possible with the Microsoft BI platform. And I really liked his recent article on using VS Code to query a Power BI file and generate measures. But as I was reading that blog post I could not help but think that it was going to be outside of the comfort zone of a lot of Power BI users.

I know a lot of BI developers that have strong skills in DAX, Power Query and SQL, but don’t often use C# and many of them don’t use VS Code and have not heard of nuget. So this made me wonder if there was a another way of doing the same thing with the tools that many BI developers already use like DAX Studio and Tabular Editor

I actually figured out a couple of approaches to achieving the same result as Phil. The first one uses the following 2 pieces of information.

  1. Tabular Editor has a brilliant feature called Advanced Scripting which lets you run little pieces of C# code and is an excellent way of automating changes in your models. And in addition to being able to use the standard properties and methods Daniel has build a series of helpful “helper” methods like AddMeasure which has parameters for <Name>, <Expression> and <Folder>.
  2. As I showed in my previous post where I generated part of a DAX query using another DAX query. It is possible with some careful string manipulation to use DAX to generate other code such as the C# using in the Tabular Editor Advance Scripting feature.

If we combine the two pieces of information above we end up with the following query which generates a Tabular Editor AddMeasure() call for each value in Product[Color].

EVALUATE 
ADDCOLUMNS(
VALUES('Product'[Color])
,"ScriptExpression"
,var _color = 'Product'[Color] 
var _name = "Sum of " & _color & " Sales Amount"
var _expression = "CALCULATE(SUM('Sales'[Sales Amount]) ,'Product'[Color] = \""" & _color & "\"")"                     
return "Model.Tables[""Sales""].AddMeasure( """ & _name & """, """ & _expression & """, ""AutoMeasures"");"
)

When you open DAX Studio from the External Tools menu and run this query you get output that looks like the following and you can selected the “ScriptExpression” column and copy that.

Then you open Tabular Editor from the External Tools menu. Click on the Advanced Scripting tab and paste in the output from the “ScriptExpression” column. Note this may include the “ScriptExpression” column header at the top which you will need to delete.

(note to self, I should add a “copy without headers” option to DAX Studio, there is an option for this, but it would be nice to add it to the right-click menu on the results)

Then when you click “run” (1) on the advance script, you will see a folder with all your new measures appear (2). You can then check that the expression has been entered correctly and click save (3) to make these appear back in Power BI Desktop.

Stay tuned for the next post in this series where I will show another technique for doing this.

The easy way to Generate a DAX query for all measures

I had an interesting request from a Data Scientist in our team recently. He’d been extracting some data from one of our tabular models, however he was having some trouble getting his predictive model working reliably.

We already had a query in the form similar to the following where we had a couple of group by columns, one or two filters and a handful of measures:

EVALUATE
SUMMARIZECOLUMNS(
   'Table1'[Column1],
   'Table2'[Column2],
   FILTER(ALL('Table1'[Column3]), 'Table1'[Column3] = "Value",
   "Measure1", [Measure1],
   "Measure2", [Measure2]
) 

Up until this point we had been manually adding measures that we though may influence the behaviour we were trying to predict, but this was a slow, trial and error based process. So the Data Scientist rang me and said “You know what? Why don’t you just give me an extract with all the measures?”. “You do realise we have over 1,000 measures” I said, “because we have lots of time intelligence variations like Current Month, Previous Month, Month over Month variance, etc.” and . “That’s fine he replied, I can always ignore any that I don’t want or that are not significant – you can just do something like a SELECT * right?”.

So if you’ve ever written your own DAX queries you would know that you can do a query like the following to get all the columns in a single table

EVALUATE 'Table'

But that will not get you any measures, you have to list out the measures manually one, by one. At this point I knew that I really did not want to spend hours to hand type a query with over 1,000 measures so I starting thinking what options I might have for generating this query.

I knew I could probably build some sort of foreach loop in Powershell using AMO/TOM. Or I could maybe use the Advanced Scripting in Tabular Editor. But I also knew that I could easily get a list of all the visible measures by querying the $SYSTEM.TMSCHEMA_MEASURES or $SYSTEM.MDSCHEMA_MEASURES DMV’s using DAX Studio

After a bit of experimenting I ended up with the following expression which builds a list of all the visible measures in the model in the "Name", Expression format that is needed for SUMMARIZECOLUMNS

SELECT	'"' + [Name] + '", ' as [Caption], '[' +  [Name] + '],' as [Name]
FROM $SYSTEM.TMSCHEMA_MEASURES
WHERE NOT [IsHidden]
ORDER BY [Name]

Then I was simply able to paste in the output from this query after the filters in our existing query and run it – Job done.

DAX Studio 2.11.1 Release

I just published a point release for DAX Studio that fixes the following bugs. You can get it from https://daxstudio.org

Fixes

  • Filters on datetime columns where we cannot parse the date value now report the error rather than crashing when using the “Edit Query” button
  • #343 Fixed spelling of KEEPFILTERS for IS NOT filters in the new Query Builder
  • #345 Comma placement changed to match the style used by DaxFormatter
  • #346 / #347 DaxFormatter now works in the Expression Editor
  • #351 error while exporting tables to SQL Server
  • #352 hierarchies in display folders not expanding
  • #357 leading/trailing comments missing when doing “define measure”

DAX Studio 2.11.0 Released

The 2.11.0 release of DAX Studio is now available and brings with it the following new features and fixes.

New Preview Features

There are 2 new preview features this month, so you need to go into Options > Advanced and enable them if you want to use them

Query Builder

When enabled, the Query Builder appears as a button in the main ribbon

It lets you drag and drop columns and measures to build up a query which can include basic filters. You can also add custom measures or override the expression of a measure from your data model.

Query Builder animation

You can either run the content of the query builder directly or you can click the “Edit Query” button to send the text for the query to the main editor window where you can run it or further customize it.

Query Benchmark

The Query Benchmark tool appears as a button on the Advanced ribbon. It allows you to run a given query a number of times both against a cold and warm cache. This is useful because even on a quiet development server there can be a number of factors that can cause variability in the server timings.

The Benchmark feature makes use of the Server Timings functionality to record detailed information about each query execution.

You get the option of how many runs of cold vs warm cache (and by default these are linked)

The output of a Benchmark run shows a summary view with the Avg, StdDev, Min and Max of both runs for the Total Duration and the Storage Engine Duration

The detailed output shows the timings of every single query execution.

New Features

In addition to the two big features above there are a number of smaller features that have been added in 2.11.0

  • #314 Added 3 Quick Access buttons for
    • New
    • New (with current connection)
    • Save
  • #277 Added Export button to all trace outputs
  • Added full filename tooltip to tabs (thanks @dmarkle)
  • Promoted View Metrics (Vertipaq Analyzer) from preview status to general availability
  • Promoted Export Data feature from preview status to general availability
  • Documentation Updates:
    • Added license page
    • including a section on SmartScreen issues in Win10 (thanks to Gilbert at fourmoo.com )
  • Updated syntax highlighting to align with DaxFormatter.com
  • Added a note in the Database tooltip that the Database Id can be copied using a right-click
  • Added formatting to shorten asazure: and powerbi: server names in the status bar so that the key information is visible
  • Added a partitions tab to the Model Metrics views
  • Added a sample of any missing keys to the relationships tab in the Model Metrics (these keys are not saved for privacy reasons when exporting to a vpax file)

Fixes

  • fixed cancelling of exports to SQL Server
  • improved keyboard navigation by adding IsDefault/IsCancel properties to dialog buttons (thanks @dmarkle)
  • fixed an issue with intellisense not re-enabling after reconnecting (thanks @dmarkle)
  • fixed an issue with Query History pane not updating the “Current” Database filter when changing databases
  • disabled external tools when connected to PowerPivot
  • #290 updated all URL references to use https (thanks @warthurton)
  • #291 fixed issue connecting from Excel 2010
  • #301 refined the code completion to prevent it overwriting code when editing in the middle of an expression
  • #302 disabled column re-ordering in Metrics view
  • #303 fixed an issue with the metadata pane when connecting to a model with dynamic format strings defined in calculation groups
  • #308 removed backtick characters in column names with spaces where using the Static Excel output
  • #320 fixed server not found error when exporting vpax file for PowerPivot models
  • #325 fixed error when attempting to connect to PowerPivot files stored on OneDrive
  • #329 fixed incorrect database name shown when launched from the Excel addin and not connected to PowerPivot
  • #330 fixed the status message getting stuck after writing output to a file destination
  • #339 fixed a bug that reported an assembly load error when Cancelling a query

DAX Studio: How measure formatting works

Have you ever run a DAX query from DAX Studio (or using a DAX window in SSMS) and wondered why the format you set on a measure does not always seem to get applied?

Let’s start with the following simple DAX query which simply lists the month number from 1 to 12 and a measure.

EVALUATE
ADDCOLUMNS(VALUES('date'[Month])
,"Internet Total Sales"
, [Internet Total Sales]
)

If we run this in DAX Studio you will see the following:

Note how the Format of the measure is correctly applied to return the dollar sign and the thousand separator and only 2 decimal places.

Now lets run the same query against the same model using SQL Server Management Studio (SSMS)

Now we have no currency symbol, no thousand separator and 3 to 4 numbers after the decimal place. What is going on here?

Well I’m going to let you in on a little secret about DAX queries:

The results in a DAX query are always returned unformatted.

You may well ask “Why is the formatting working in DAX Studio then?“. The answer is simple, I’ve specifically added code that looks at the column names returned by a query and then looks for a measure with the same name and applies any format string it finds.

You’ll notice in the example query that I’m setting the column name to the same name as the measure. If I change the column name to “AAA” you will see the following output.


Which is the same “raw” format we see from SSMS.

And if we exploit this for evil purposes we can even change the column name in the output to match a completely different measure. In the screenshot below I am applying the “Margin %” format to the [Internet Total Sales] measure so that it has one decimal place and a percentage sign and the decimal place is shifted two points to the right. I can’t think of a practical use for this behaviour, but you may see it occasionally if you are editing a query and change the measure reference without updating the column name.

You usually never see this in a client tool like Power BI as it builds the DAX queries internally so it knows which measures map to a given column in the result set so it can then apply the formatting appropriately.

If you’ve been following along with some of these example queries there is one other formatting feature we have in DAX Studio which you may have run into and that is the “Automatically Format Results” setting under File > Options.

This is off by default, but if you switch it on DAX Studio will apply some basic formatting based on the data type of the column in an attempt to try and make the results easier to read.

If the column is an integer use the format string “#,0” (this should include the appropriate thousands separator based on the language settings of your pc)

If the column is a decimal use the format “#,0.00”

If the column is a decimal number AND the name includes “pct” or “%” then use the format “0.00%”

This formatting of results is just one of the many small ways that I try to improve the user experience when working with queries in DAX Studio.

DAX Studio 2.10.2 Released

I’ve just released the 2.10.2 update to DAX Studio.

Starting with the 2.10.0 releases the crash reporting has been improved as there was previously an edge case where certain types of crashes were not triggering the crash report dialog. So this release includes a number of stability updates that have come from people submitting crash reports.

There are also a few smaller features that are detailed below

New Features

  • #274 Adding Editor Word Wrap to options screen
  • #270 Added option to display current user in title bar
  • Added Dax Formatter option to omit spaces after function names

Fixes

  • fixed crash when selecting PBI / SSDT connection, but not selecting a file
  • fixed crash when running a query that produces no rows and using the Static Excel Output
  • fixed #272 Vertipaq Analyzer not working if any tables have a single quote in the name
  • fixed a number of crashes reported via the crash report dialog
  • improved a number of background threading calls to use the simpler async/await pattern
  • moved checking for schema changes on activating DAX Studio to a background thread to prevent the UI freezing for slow connections (AAS) or if there are active commit locks.

DAX Studio 2.10.1 release

It seems that the last few years I’ve always seemed to do a pre-Christmas release for DAX Studio and this year is no different. The latest update of DAX Studio has just been released which includes a number of new features and bug fixes which are outlined below.

You can download the latest release from https://daxstudio.org

Database Tooltip

Database Tooltip

When you hover over the database dropdown in the metadata pane we now display an informational tooltip which includes the compatibility level of the database, the culture of the database and the date the data was last refreshed.

New Options

A couple of new settings have been added File – Options screen

The first lets you configure whether the editor inserts tabs or spaces when you indent code. This can be useful if you use other code editors and wish to keep a consistent style across them all.

The second lets you configure a custom export format by specifying a delimiter to use and whether or not to always quote string fields (however if your strings include your delimiter field they will still be automatically quoted as not doing this would not allow the file to be consumed properly).

This configuration appears as an option when you save query results to a file target or when you use the Export Data Wizard

Vertipaq Analyzer

A new Summary Pane has been added to the Vertipaq Analyzer view. This page gives the user a single place to see summary information about the model including the total number of tables and the total size of the model.

The tables pane now has a new column that indicates if any of the relationships for that table have RI violations. This uses the DMV that Phil Seamark pointed out in his recent blog post

You can now also import metrics that were previously exported

Date Table indicator

The metadata pane has been updated to indicate if a table has been marked as a “Date” table.

Ribbon buttons for Issues and Feature requests

I have seen a few issues posted in various other forums like the Power BI community forum over the years so I’ve decided to add buttons to the help ribbon to allow a quick and direct way of posting issues directly to our github issues list (although a github login is still required)

Search for Functions and DMVs

Both the Function and DMV panes now have a search box.

Fixes

And in addition to the above features it includes numerous bug fixes including the following:

  • #224 Server Timings and Query Plan traces now work with the EffectiveUsername connection string parameter
  • #237 fixed population of UserName field in traces
  • #223 Export Wizard now replaces characters that are not legal in file names with underscores
  • #222 UTF-8 csv exports no longer include BOM characters
  • #218 Vertipaq Analyzer – fixed an issue trying to export a model with a multiple of 50 +1 columns
  • #238 Vertipaq Analyzer – fixed Column Header name for Data Type column
  • Vertipaq Analyzer – fixed not showing any output for certain models #235 #231 #218
  • #213 fixed a bug that was not allowing “DEFINE MEASURE” to work with KPIs
  • #241 fixed File – New creating 2 blank windows (thanks @Mankarse)
  • #226 removed OnDragEnter debug message

Known Issues

There is an ongoing issue with some people getting an error from the Excel Add-in reporting a MissingMethodException which is preventing connections to PowerPivot models. However I’ve double checked a copy of the dll in question and it does contain the method so this error does not make any sense. And I have been unable to reproduce this issue on any of the systems which I have access to. If anyone is able to assist with more information on how to reproduce this issue I’d love to hear from you as I’d like to get this issue fixed.