Random Procrastination

Darren Gosbell [MVP]

Tag: daxstudio (page 1 of 2)

DAX Studio v3.0.1 Released

I’ve just pushed out an update to DAX Studio with a number of fixes and a few small updates. You can download it now from https://daxstudio.org

Updates

  • Added query information button to traces
    (This captures the actual query text and any parameter values plus the ActivityID)
  • Updated Query History icons
  • #949 Exposed Theme/Mode setting in Options
  • Added MPARAMETER keyword to syntax highlighting

Fixes

  • Fixed #891 Error exporting to static Excel file
  • Fixed #889 crash when dragging trace windows
  • Fixed #886 incorrect link to tutorial in Getting Started dialog
  • Fixed #894 Define and Expand sometimes producing incorrect output
  • Fixed #924 username blank in All Queries trace
  • Fixed #930 increased default code completion window width
  • Fixed #931 automatically select all text in find/replace dialog
  • Fixed #906 improved waterfall layout in Server Timings
  • Fixed setting focus in the editor when changing tabs
  • Fixed file icon registration
  • Improved error handling around shutdown operations
  • Added extra hotkey validation rules
  • Fixed #916 added retry logic to all clipboard operations
  • Improved Power BI filename detection for non-English cultures
  • Removed unused dependencies and reduced installer/portable file size

DAX Studio v3 released!

I know it’s been a while since I’ve released an update to DAX Studio. But I’m happy to announce that version 3.0 is now available with a major update to the User Interface including the option for a Dark theme. You can download it from https://daxstudio.org (which has also had a major update and can be viewed in Dark mode) and you can read about what’s new in this release here: v3 Release | DAX Studio

DAX Studio 2.17.2 released

DAX Studio 2.17.2 has just been released and the following is a run down of the new features and fixes that you will find in this release.

Apologies to anyone who attempted to download the 2.17.0 release – there was an update to the code signing tool and it failed to correctly sign the installer. The automated build has now been fixed so that it correctly signs the installer again.

New Features

Added Parameter support to Query Builder filters

The Query Builder now supports parameters. Typing an @ as the first character in a query builder filter value will switch the value into parameter mode and the following text will be treated as the parameter name. (typing @ a second time or clicking on the purple @ button will toggle the parameter back to the standard value mode)

When you run the query builder where a filter has a parameter you will be prompted for the parameter value just as if you had typed out a query that included a parameter reference. This allows you to build a query with parameters that can be pasted into a paginated report or any other tool that supports DAX parameters.

Running Query Builder with a filter parameter

Removing <pii> tags from xmSQL in Server Timings to improve readability

Power BI and AzureAS have added <pii> tags around some of the text in the query events that DAX Studio captures as part of the Server Timings. These tags allow Microsoft to strip out information that could potentially be personally identifiable when writing them to centralized logs. But because DAX Studio does not send any of this information off your machine and because this information also includes important contextual information for troubleshooting and performance tuning we are just removing these tags to reduce the noise and improve readability.

Fixing accuracy of Server Timings for queries with Batch events

Prior to this update Storage Engine batch events timings were not being added to the SE duration figures. For a lot of models this difference is insignificant, but for large queries against large models this can result in an incorrect inflation of the FE duration. Keep an eye on the posts from Marco and Alberto at sqlbi.com as they are working on an in-depth video to explain this improvement to the Server Timings.

Query Builder queries are now captured in the query history

Prior to this update the Query History pane was only capturing the text of queries that had been typed into the query editor. Now the Query History pane captures any query that was executed regardless if it was from the edit window or from the Query Builder.

There is now an icon next to each query history row indicating where it was captured from and if you double click on a query that was originally run in the Query Builder it will be restored back into the Query Builder window

The /f parameter can now open vpax files

Now when you run DAX Studio from the command prompt the /f parameter will open either .dax or .vpax files.

Added extra information to the error message when the XMLA endpoint is not correctly enabled

If the Power BI XMLA endpoint is not properly enabled the Microsoft ADOMD provider returns a very generic error message making it hard to know what has gone wrong. DAX Studio now adds some extra text after the Microsoft error if you are attempting to connect to an XMLA endpoint and hit this generic WebException error to suggest you check the XMLA endpoint settings in your tenant.

Improved caching of measure expressions

Prior to this update features that required access to the text of the multiple measure expression such as the “Define All Measures” feature would request the expression text one at a time, now all of the measure expressions are retrieved in a single request.

Fixes

  • Fixed #703 preventing a crash when entering a proxy password
  • Fixed #715 added DOUBLE data type to intellisense and syntax highlighting
  • Fixed #740 export to Excel failed on some machines
  • Fixed #755 saving parameters to XML was not correctly encoding special characters
  • Fixed #750 would crash on startup if the WMI information on the machine was corrupt
  • Fixed download link in Help – About screen

DAX Studio v2.16.2 Released

DAX Studio v2.16.2 has just been released and is available at daxstudio.org. It includes the following fixes and improvements. The first one being particularly important:

Traces fail to start with a timeout error

Fixed issue #664 – A small refactoring of the trace engine for some future work has accidentally resulted in the engine not being able to detect if a trace has been started or not, resulting in timeout errors. This appears to affect most scenarios apart from one or two edge cases. Unfortunately it was one of those edge cases that was used when testing that the previous code change had not broken anything. The 2.16.2 code base has been tested against all the different engine types PowerPivot, Power BI Desktop, Power BI Desktop as an External Tool, a local SSAS Instance, a remote SSAS Instance, Power BI Premium XMLA Endpoints and Azure Analysis Services

Showing View Metrics Last Refreshed and Analysis Date in the local timezone

Fixed issue #663 – Previously the Last Data Refresh and Date of Analysis were both displayed in UTC in the Summary tab of the View Metrics pane. Internally the dates are still stored in the vpax file in UTC so that that if you send the vpax file to someone in a different timezone the times will n

When saving a file with the Query Builder open custom measure names were not saved

Fixed issue #659 – if you save a file with the query builder open which has custom measures that were created within the query builder then the name of those custom measures was not saving properly prior to this fix.

Debug commas not handling lines with indented comments correctly

Fixed issue #663 – the new debug commas preview feature was not correctly positioning the commas if one of the lines of the query had an indented comment. This issue and a few other ones around different comment placements have been fixed.

View Metrics – Last Refreshed date was incorrectly adjusting from UTC time

Fixed issue #670 – The Last Refreshed Datetime in the View Metrics summary page was incorrectly getting the timezone offset applied twice when calculating the UTC time. This resulted in the time being moved too far forward if your local timezone is behind UTC or too far back if your local timezone is ahead of UTC.

DAX Studio v2.16.1 Released

This update to DAX Studio includes all the new features introduced in 2.16.0 but includes the following bug fixes:

  • #657 Unable to open a new query tab either with a new connection or with a copied connection
  • #658 passing the database name on the command line
  • #647 hidden objects not hidden correctly when show hidden objects option was turned off (this was partially fixed in 2.16.0 but should work in all scenarios now)

Learning to write queries in DAX – Demo Script from DataANZ 2021

If you were in my recent session at the DataANZ 2021 conference I mentioned that I would make a copy of the queries I created during that session so here they are!

In my demos I used the Adventure Works 2020 pbix file from here https://aka.ms/dax-docs-samples

And I added the following simple measure to the Sales table:

Total Sales = SUM( Sales[Sales Amount])

You can download the script of the demo queries from my OneDrive

DAX Studio 2.15 Released

This release includes a lot of small fixes, but hopefully I think I may have finally tracked down and fixed what was causing the code completion to randomly stop working.

You can get it from https://daxstudio.org

New Features

  • #559 Added a toolbar button and right-click menu to copy Server Timing metrics
  • #556 Added an option to play a sound after long running operations (this can be enabled in the Options menu)
  • Added “Duplicate Measure” right-click menu to Query Builder
  • Added Error events to All Queries trace, this can be helpful if you have another client tool that is generating invalid DAX or MDX
  • #568 Added right-click menu option in the editor to go to dax.guide for functions
  • #569 Added Function tooltip display when hovering the mouse over functions in the editor
  • Added setup cmdline parameter docs
  • Added SQLBI videos to docs
  • Added discussions link to website navigation
  • Updated Multiple Query Detection to work with 
    // Dax Query in addition to // SQL Query comments

Fixes

  • Adding extra checks around QueryEnd timeout in Server Timings
  • Close the Trace Layout ribbon tab when all documents are closed
  • Made ‘Add New’ disabled in the Query Builder if the current model has no tables
  • Fixed #458 added docs and tooltip to explain memory sizes
  • Fixed #517 updated clipboard bindings to allow for copying from the View Metrics tabs
  • Fixed #539 error starting traces when using Roles or EffectiveUserName
  • Fixed bug in retry for ViewMetrics when there is an error reading statistical information.
  • Added configuration to read statistical data for VertiPaq Analyzer. Disable reading statistical data for legacy versions of SSAS. Added retry logic and log of warning if there is an error reading statistical data.
  • Fixed #525 F1 and Context Menu to open dax.guide for functions
  • Fixed server timings trace for PowerPivot
  • Fixed drag drop issues with QueryBuilder
  • Fixed #514 Server Trace not working in Excel
  • Fixed #520 option to not truncate tables was not working when exporting to SQL Server
  • Fixed #511 Excessive metadata refreshes was causing metadata pane to collapse
  • Fixed #512 fixed Table tooltip warnings and added measure count
  • Fixed #546 corrected wording in error message
  • Fixed #549 correctly escape connection string values containing special characters
  • Fixed #557 delimiter error on Clear Cache when using non-US delimiter style
  • Fixed a bug with the Roles parameter in the advanced connection properties not accepting spaces
  • Fixed #563 metadata incorrectly resetting when using a combination of mouse selection and query builder hotkeys
  • Fixed #564 Application crashes when clicking on a ribbon tab while the ribbon is collapsed
  • Fixed crash when resetting key bindings
  • Fixed #575 autocomplete sometimes stops working
  • Disabled the Add Measure button when connected to a model with no tables
  • Fixed #576 Editing an existing Measure in Query Builder against PowerPivot causes a crash

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.

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”
« Older posts