Random Procrastination

Darren Gosbell [MVP]

Author: Darren Gosbell (page 1 of 3)

DAX Studio 2.16 Release

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

New Features

Added Sponsor page to daxstudio.org

We are now able to accept sponsorship via Github Sponsors. If you would like to make a financial donation to support the continued development of DAX Studio you can now sponsor the project at https://github.com/sponsors/DaxStudio

Added preview Data for tables and columns

A new right-click menu has been added to tables and columns which will display a preview of the first 500 rows of data.

Extended Parameter Support to data types other than text

Previously parameters were always passed through as text based parameters. As of v2.16 you can now specify the data type for your parameters. This support is available both in the parameter dialog and in the XMLA parameter blocks.

When you specify @parameters in your DAX queries they will still default to the string data type, but there is a new dropdown which allows you to change this to one of the other supported datatypes (this list matches the parameter data types supported by Report Builder and Power BI Report Builder)

And if you choose the Write Parameter XML button or if you capture the query parameters using the All Queries trace those data types will also be reflected in the XML parameter block.

Added new version notification in title bar

When a new update is available there will be an update flag in the top right of the application and clicking on that flag will take you straight to the download page for that version.

Updated xmSQL cleanup to also fix table names

In the previous release of the xmSQL update feature we were cleaning up column names to match the display names, in this release this functionality was extended to also clean up table names.

Supporting all of the /f /s /d command line parameters at the same time

DAX Studio has always had support for the following command line parameters

/f <filename> – to open a .dax file
/s <server> /d <database> – to connect to a server and database

But prior to this release you could only use /f on it’s own. As of this release you can use all 3 of these parameters together.

Query Builder Enhancements

The Query Builder has a number of new features in this release which are outlined below.

Query Builder Updates
  1. There is a new Clear button at the top of the Query Builder which gives you a convenient single click to clear everything from the Query Builder.
  2. Now when you drag a column which has a Sort-by column the sort by column is added to the output list (this is required to support the next item)
  3. There is a new Order By pane, this is collapsed and all columns are sorted in ascending order by default.
  4. But you can expand this section and click on the columns there to switch them between sorting Ascending, Descending or ignored.
  5. There is a new Auto-Generate button. When you click on this button the query generated by the query builder will appear in the edit window and will be updated in real time as you make changes to the Query Builder
  6. Clicking the Edit Query button will now update any previously generated query rather than adding a new version underneath.

Extending installer support for Windows ARM64

The installer used to do a platform check for x86 or x64, this has been extended to allow installing on ARM64 since that platform now has an x64 emulation layer.

Other Enhancements

  • #620 Adding Ctrl+W to select current word
  • #584 Extending Hover tooltip to work with Keywords
  • Custom Key-Bindings moved out of preview
  • #636 Show PBIX filename instead of database ID in the database dropdown when connected to Power BI Desktop
  • #638 Adding encoding option when exporting to CSV
  • updating ADOMD / AMO references to 19.22

Preview Features

Debug Commas

This command moves commas in formatted queries to the start of the line. This re-formats the selected text so that you can more easily comment out sections of code while debugging.

Note: this preview feature replaces the swap delimiters button in the ribbon.

Show XMLA Commands in All Queries trace

This option is useful for viewing commands sent by other tools like SSDT and Tabular Editor.

Fixes

  • #578 Analyze in Excel not working with PBI XMLA or AzureAS
  • #579 Server Timings Layout Tab not appearing
  • Improved error message when no query text is specified
  • #581 All Queries Trace against PowerPivot caused crash
  • Fixed a crash when attempting to add a display folder to the Query Builder
  • Fixed a crash when attempting to use the query builder against a model with an invalid calculation script
  • #592 datatypes not syntax highlighted and not in code completion
  • #613 DMV and Function drag/drop not working
  • #647 Hiding hidden objects was not working correctly with items in display folders
  • #643 fixing issue with code completion window “sticking”

Fixing incorrect date formats in Power BI Report Server when using Chrome or Firefox

Reposted from 1 Mar 2019 from my old blog since geekswithblogs.net is down again at the moment: http://geekswithblogs.net/darrengosbell/archive/2019/03/01/fixing-incorrect-date-formats-in-power-bi-report-server-when.aspx

We are using Power BI Report server to publish Power BI Reports internally. And being in Australia we use a “sensible” date format of dd/mm/yyyy Smile 

However at a recent showcase with some of our end users they commented that our date format was “backwards” ie. mm/dd/yyyy

If you are using US regional settings on your PC you probably have not come across this issue, but for other regions this can be a problem.

Upon getting back to my desk I checked the report in Power BI Desktop and the date format was correctly picking up my local regional settings. I then opened the report in IE (which is what most of our End Users have as a default browser) and the report was still using the Australian format (dd/mm/yyyy):

DateSlicer-IE

However when opening the same report in Chrome or Firefox the date was using US regional settings (mm/dd/yyyy):

DateSlicer-Chrome

After doing some google/binging I found that both Chrome and Firefox have their own internal regional settings dialog and they both default to using US settings.

If you go into the Settings menu in Chrome and search for “Language” you will find the following, and it’s not just the default display language that matters the order is also important. Moving “English (Australia)” to the top of the list, then deleting cached images and files fixed this issue.

RegionalSettings-Firefox

Clicking on the 3 dots to the right of the language gives you an option to move that language to the top.

Firefox has a very similar language dialog and the same fix works for it too.

What this does behind the scenes is to change the “Accept-Language” headers that the browser sends to PBIRS to make your preferred language first in the list. This in turn alters the regional formatting setting javascript file that is sent down to the browser. Before this change I could see a file called angular-locale_en-us.js being used, after the change I now see angular-locale_en-au.js

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

Fixing “Attempted to read or write protected memory” Refresh Errors in Power BI Report Server

Warning: I am just sharing what worked for me in our environment in the hopes that it may help others who might hit this issue. But you should make sure to test changes like this yourself and that you have a good backups and a rollback strategy before changing your own production environments. Any changes you make to your servers are done so at your own risk.

After doing a fairly extensive round of testing we recently updated our production environment for our Power BI Report Servers to the Jan 2021 release. However unfortunately during the verification testing after the upgrade I found an issue on the production servers that had not occurred on any of our other environments.

The issue was that doing a scheduled refresh of a pbix file linked to an access file was failing with an error that “We’re sorry, an error occurred during evaluation.; Attempted to read or write protected memory” and searching for this error message only brought back hits from 2-3 years ago which were not very helpful.

The error reporting in the Report Server UI is often not very comprehensive, but even checking the RSPowerBI* logs in the Report Server log folder did not help all that much (I’ve included the relevant lines from an example of this error at the end of this post)

Initially I tried running a repair operation by re-running the Power BI Report Server installer, I could see that one of the things it checked was the Access 2016 Engine, but unfortunately even after doing a reboot the issue persisted.

Upon checking in “Add or Remove Programs” I found that the servers had both the Access 2010 and Access 2016 Engine runtimes installed. I thought this was a little odd since the 2016 version should supersede the 2010 version and these servers only run Power BI Report Server so it was not like there was other software that my have had specific requirements for the 2010 engine. So I did wonder if something had gone wrong with the registration of these 2 versions and somehow the server was expecting to run using the 2016 version, but was actually running using the 2010 version.

However upon checking our non-prod environments I found that they all had both versions installed. But by watching the installer I could see that the Jan 2021 release was definitely only installing the Access 2016 runtime. So as a test on one of our non-production environments I tried uninstalling both runtimes then only re-installing the Access 2016 Engine runtime (which I found by searching for “access 2016 engine download“).

I did not get prompted to reboot after doing the re-install of the Access 2016 engine so I simply did a stop and start of the Power BI Report Server using the Report Server Configuration Manager. After doing this refreshing one of my test reports using an Access file data source still worked. So since production was already in a broken state as far as Access refreshes were concerned I tried this same process on the production environment and this fixed our issue so we now have a fully operation install of the Jan 2021 release of Power BI Report Server.

For reference the full error message from the RSPowerBI log files is below:

2021-02-24 18:49:46.7357|ERROR|19|Error Processing Data Model Refresh: SessionId: b3c84c62-2cf0-4df0-b22c-fde90857c4b8, Status: Error Failed to refresh the model, Exception Microsoft.AnalysisServices.OperationException: Failed to save modifications to the server. Error returned: 'We're sorry, an error occurred during evaluation.;Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
Attempted to read or write protected memory. This is often an indication that other memory is corrupt.. The exception was raised by the IDbCommand interface.
'.
at Microsoft.AnalysisServices.Tabular.Model.SaveChangesImpl(SaveOptions options)
at Microsoft.PowerBI.ReportServer.AsServer.TOMWrapper.RefreshModel(Database database)
at Microsoft.PowerBI.ReportServer.AsServer.AnalysisServicesServer.RefreshDatabase(String databaseName, IEnumerable1 dataSources, IEnumerable1 parameters, String clientSessionId)
at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.AnalysisServicesDataRefresh.RefreshDatabase(AsDatabaseInfo asDatabaseInfo)
at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.DataRefreshScope.<>c__DisplayClass34_0.b__0()
at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.DataRefreshScope.d__33`1.MoveNext()

Power BI Report Server – fixing the “Server is not reachable” Error

If you’ve ever worked with Power BI Report server there is a handy button in the top menu that says “Edit in Power BI Desktop”.

And as you would expect this button will open the report you are currently viewing in Power BI Desktop where you can edit it. All in all a very handy button, particularly when you get sent a link to a report and get asked to help fix something or explain some data issue.

The Problem

However recently I started seeing the following error stating “Server is not reachable” any time I tried to use this button

Power BI, Power Platform, Data Platform: In Action: Technical Preview of Power  BI Reports in SSRS

This is a bit strange as I’ve used this button many times before. So I tried going another way and using to File > Open in the Report Server version of Power BI Desktop and tried to open the report from the Report Server that way.

However I was again met with the same “Sever is not reachable” error which is not very helpful as I was definitely able to reach that server and view reports on it.

Error_Message.png

When this first happened I just went back to the tile view in the Report Server portal and chose the download option from the “…” menu on the report, then I was able to open the local copy and sort out the issue I had been asked to look at.

But today it happened to me again and I decided to do some digging to see if I could get to the bottom of this.

An initial solution

Doing some searches turned up a number of people hitting similar issues.

There was this one where the person was trying to connect using the webservice url instead of the portal url. But I knew that was not the issue in my case.

Then I stumbled across this one which suggested adding your credentials to the Windows Credentials section of Credential Manager. This actually worked for me, but I was not overly happy with this approach as in a few month time when my password expires I’d have to remember to come in here again and update my password. The more likely scenario would be that I would forget to do this and a few weeks later I’d realise that the “Edit in Power BI Desktop” button had broken again.

However while I was in the Credential Manager I clicked over to the web credentials and noticed the following interesting entry that started with SsrsSessionResourceKeyPrefix…

I knew that Power BI Report server was built on top of the old SSRS portal and when I expanded the entry it showed that these credentials had been saved by “Power BI”.

The Final Solution

Removing this entry and also removing the entry I just added to the Windows Credentials section fixed the issue properly. Now I don’t have to update a stored password and both the Edit in Power BI Desktop button and the File – Open option are both working as they should.

DAX Studio 2.14 Release

Updates

The following are some of the new features and improvements that you will find in v2.14 of DAX Studio

Blank Canvas Help Text

Help text has been added when the edit control is empty. This is designed to help people that are new to writing DAX queries. There are links to an article with information on writing DAX queries as well as a link to display the query builder. This help text will automatically fade away as soon as you start typing and once you no longer need it you can turn it off.

New Privacy Settings

There are new privacy settings now available in the options. We don’t recommend enabling these for general use, but if you are working with data of a highly sensitive nature and you need to ensure that DAX Studio does not send any information externally you can enable these options.

Note: DAX Studio never sends any data from your model externally.

However if the text of your queries contain sensitive information such as customer names or account numbers and you choose to use the Format DAX option that query text gets sent to daxformatter.com. And if you happen to suffer a fatal crash the crash reporter includes the option to send a screenshot and if you don’t untick the option to include the screenshot there maybe some data visible or some information in the query text.

So you as the user have complete control over any information that is sent externally. But if you work in a highly secure environment and you need to ensure that no accidental information leakage can occur then you can enable some or all of these options.

Enabled Discussions on the Github site

Discussions are a new feature on Github and this now gives us a place where people can ask questions or make suggestions without having to log a formal issue.

Multiple Queries Detected dialog

The “Multiple Queries Detected” dialog will appear when pasting in queries from Power BI Performance Analyzer that came from a Composite model. Such queries will sometimes have 2 queries where the second query is prefixed with a // SQL Query comment. The query after this comment will sometimes be SQL however if your model is a composite model over AzureAS or Power BI it might be in DAX.

New Server Timings Options

There are new options for Server Timings that help make the xmSQL text easier to understand. These new processing options are enabled by default and strip out internal IDs, Aliases and Lineage guids and highlight Formula Engine callback operations to make these events easier to understand

Other Miscellaneous Updates

  • Help Ribbon updates
    • Updating link in help ribbon to Analysis Services forum
    • Adding link to Discussions https://github.com/daxstudio/daxstudio/discussions
  • Query Builder moved out of preview status
  • Benchmarking moved out of preview status
  • Adding Query Builder documentation to daxstudio.org
  • Updating AdomdClient and TOM references to v19.14
  • Added retry logic when fetching Database and Model lists
  • #450 Database Last Updated Datetime is now shown in local time instead of UTC (and the timezone information is displayed)
  • Added keyboard short cuts in metadata pane to work with Query Builder
  • #492 added description to function tooltip

Fixes

  • Fixed crash when doing Replace All with an invalid RegEx expression
  • Fixed crash when clicking Refresh Metadata after connection has dropped
  • Fixed #469 Server Timings busy spinner was activating incorrectly when doing Clear Cache
  • Fixed #467 Database Name was being incorrectly injected into all new connections when DAX Studio was opened from External Tools in Power BI Desktop
  • Fixed #466 PBI XMLA connections not being parsed correctly
  • Fixed #457 crashing on startup after setting view hidden objects = false in options
  • Fixed #356 Clear Cache throwing errors against PBI XMLA endpoint
  • Fixed #449 Connection lost during Benchmarking
  • Fixed a trace timeout issue when connecting to PBI XMLA endpoint (sometimes it is still necessary to increase the default timeout to 90 seconds or more)
  • Query Builder
    • Fixed a bug when filtering dates that reported an error parsing an empty string
    • Added the ability to drag KPI components into the Query Builder
    • Fixed a crash when trying to alter the expression for a dynamic format string for a calculation group item
  • Fixed hang when pasting in code with extremely long lines (tens of thousands of characters)
  • Fixed #451 up/down keys not work consistently
  • Fixed #446 Aggregate rewrite events causing an error
  • Fixed #444 to use correct culture in csv export
  • Fixed #445 defaulting the selected model to the base model not the first perspective
  • Fixed an issue with the trace layout options not displaying
  • Fixed a bug with swap delimiters feature and block comments
  • Fixed drag/drop of text not working within editor
  • Fixed #475 proxy auth error when entering AAD credentials
  • Fixed #485 Swap delimiters incorrectly affecting functions with periods in the name
  • Fixed #486 Define Measure not using Separator specified in Options
  • Fixed #497 Define Measures not working when connected to a model with active translations
  • Fixed #499 “Show Objects that Reference this Column” feature did not include table name.
  • Fixed #501 avoiding bug with EXCEPT function in Query Builder

Miscellaneous Crash fixes

In addition to the fixes listed above a number of other fixes have been made that should address some issues that came in via crash reports, but the majority of these reports were submitted anonymously. What this means is that for many of them it is impossible to verify that the fix has worked as we have no steps to reproduce the issue and the fix is based on assumptions and best guesses of the root cause of the issue.

If you are experiencing a regular crash or issue I would encourage you to open a bug on github so that I can help resolve your issues.

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

The best way to generate data driven Measures in Power BI using Tabular Editor

This is a follow-up to my previous post on using a combination of DAX Studio and Tabular Editor to generate a series of measures based on the values in a given column. Which in turn was inspired by this excellent post by Phil Seamark.

While I was writing my previous post I sent a question to Daniel who is the author of Tabular Editor, asking if there was a way of getting the connection details when Tabular Editor was launched from the External Tools. The idea was that I should be able to do everything Phil did in his original script from the Tabular Editor Advanced Scripting feature.

Within an hour he actually came back with an even better approach which he had just added to Tabular Editor. As of Tabular Editor 2.12.0 the database object in Tabular Editor now has 3 new helper methods.

  • void ExecuteCommand(string tmsl)
  • IDataReader ExecuteReader(string dax)
  • DataTable ExecuteDax(string dax)
  • DataSet ExecuteDax(string dax)
  • object EvaluateDax(string dax)

Update 30 Aug 2020 There was an issue with certain queries and the ExecuteDax method. This has now been fixed and Daniel has extended this method to return a DataSet (which can contain multiple DataTables) and he has also added the EvaluateDax method which can evaluate simple DAX expressions.

Also these methods are also exposed as static methods. What this means is that you can just call ExecuteReader(query) instead of Model.Database.ExecuteReader(query)

These let you run DAX or TSML scripts directly against a connected data model. The advantage of these methods is that you don’t need to do any searching for port numbers or loading additional libraries and they make use of the existing authenticated connection, so there is no need to re-authenticate to the data model.

So with a simple 14 line script and no additional tools or downloads you can generate a set of data driven measures for your model. Simply click run (1) and your new measures appear in your model (2) and once you save them they will appear in Power BI Desktop.

Below is the text version of the advanced script if you want to modify this to work in your environment. This script is just a tweaked copy of the main loop in the original one Phil posted with a few small syntax changes as the Advanced Scripting appears to use a slightly older version of the c# compiler which does not have some of the newer features like interpolated strings.

string query = "EVALUATE VALUES('Product'[Color])";

using (var reader = Model.Database.ExecuteReader(query))
{
    // Create a loop for every row in the resultset
    while(reader.Read())
    {
        string myColour = reader.GetValue(0).ToString();
        string measureName = "Sum of " + myColour + " Sales Amount";
        string myExpression = "CALCULATE( SUM('Sales'[Sales Amount]), 'Product'[Color] = \""  + myColour + "\")";
                            
        Model.Tables["Sales"].AddMeasure(measureName, myExpression, "AutoMeasures");
    }
}

If you are interested in more information on what can be done with the Advanced Scripting feature you can check out the documentation and the useful script snippets page. While you are there make sure to have a look at the Custom Actions as these are a great way to trigger generic scripts that you use on a regular basis.

There is also a public github scripts repository with example scripts which you can download and use.

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.

« Older posts