Random Procrastination

Darren Gosbell [MVP]

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.

DAX Studio 2.12.1 Released

There are just a few minor updates to DAX Studio in this release

New Features

  • Pasting a connection string into the server name of the Connection dialog will parse out the Data Source and Initial Catalog settings

Fixes

  • #391 – issues when copying Power BI Performance data and pasting into Excel
  • #393 – application crashed if you attempted to open the Initial Catalog list before specifying a server
  • #394 – output to text file was not working for multiple results sets
  • #397 – “All Queries” trace was not working against PowerPivot connections

DAX Studio 2.12 release

DAX Studio 2.12 has just been released and I wanted to take some time to walk through some of the significant new features in this release.

The Installer is now signed with EV Certificate!

The fact that Installer now signed with an EV certificate might sound like technobabble but this should hopefully prevent all the issues and warnings people have been getting from from Windows SmartScreen when attempting to download and install DAX Studio.

A HUGE thank-you goes out to the guys at SQLBI.com. To acquire an EV certificate not only requires a financial outlay, but you also need to have a legal company entity with a physical office and financial records and things like that. Both of these things make it pretty much impossible for a free/open source tool to obtain an EV certificate on their own without assistance.

Note: Even though Windows may show SQLBI as the publisher this is due to the fact that they have funded the code signing certificate and it is a condition of the EV certificate that it has to bear the legal name of the entity that purchased it. DAX Studio is still an open source tool and all support requests still go through our github issue register.

Initial Catalog option added to Connection Dialog

An Initial Catalog setting has been added to the Advanced Options in the connection dialog, if you have a very large model or slow connection (like AzureAS or VPN) then you can now choose the initial database you are connected to. This saves you having to wait for the metadata to load for the default database, then change to another database and wait for the second lot of metadata to refresh.

In future I also want to look at improving the ability for DAX Studio to automatically re-connect to dropped connections. But that will have to wait for a future release.

Format Query button updated

The Format Query button has been changed into a split-button so that you can choose the style of formatting by clicking on the bottom half of the button.

Clicking the top half of the button will still trigger the default formatting style (which can be changed in the Options menu)

Portable version

DAX Studio now has a portable download option. The portable version is just a zip file which you can unzip then run by clicking on DaxStudio.exe. This is helpful if you need to run DAX Studio on a machine where you may not have admin rights or where you do not want to run the installer.

The portable version has the following limitations:

  • The Excel addin is not included (as we need to write to the registry with admin rights to register the addin with Excel)
  • The Power BI Desktop External Tools feature will not work (as it requires admin rights to add the pbitools.json file under c:\program files\…)
  • The ability to double click on a .dax file to open DAX Studio will not work (this also requires making registry changes to the machine)

Otherwise the portable version is identical to the installed version. Apart from the above limitations the only difference is that settings, query history, autosave files and error logs are all redirected to local files and folders underneath the folder where daxstudio.exe is launched from

Other New Features

  • Added Roles and IsAdmin properties to Database tooltip that can be useful when testing security settings.

Fixes

  • #378 Power BI External Tools – installer not correctly updating path in daxstudio.pbitools.json file if a non-default install location is used
  • #377 exporting tables with no rows would throw an error
  • #376 autosave not recovering open files for all types of crashes
  • #374 improved tooltip sampling to prevent full column materialization
  • Fixed crash when attempting to export using invalid manual connection string
  • #381 re-connecting to a pbix file that has been closed and re-opened after launching from External Tools does not work

DAX Studio 2.11.2 Released

I’ve just pushed out a minor update for DAX Studio, it fixes a few issues I’ve received from crash reports as well as the following items:

Updates

  • Adds support for the “External Tools” ribbon in Power BI Desktop

Fixes

  • #373 Fixes “Define Measure” when connected to a SSAS instance without admin rights
  • #372 Duplicate MyMeasures created when adding more than 1 custom measure in the Query Builder
  • #367 Measure information duplicated in VPAX files
  • Fixed crash when highlighting
  • Fixed crash when closing last tab and connection had been lost

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”

Power BI Report Server – fixing Pending subscriptions

Last week I came across an issue where a number of our paginated report subscriptions at work failed to go out. When we checked in the Portal these subscriptions had a status of “Pending”. This happened on our production Power BI Report Server instance which is currently running the Sep 2019 release.

This was very strange as these subscriptions had been running for months and months without issue and just stopped all of a sudden. It was even more confusing as we had some reports with multiple subscriptions and some of the subscriptions were still working while others were stuck with a status of “Pending”.

With no other information to go on in the Portal I started looking through the log files. This was complicated in our case as we have over one and a half thousand users and hundreds of report subscriptions. We also have 2 instances of PBIRS (Power BI Report Server) behind a load balancer which means two sets of log files to search through.

So I started by going to one of the pending subscriptions and clicking on the edit option

When you do this you will see the URL change to something like the following where there is a guid at the end with the SubscriptionId:

By default PBIRS writes a number of log files out to a folder at C:\Program Files\Microsoft Power BI Report Server\PBIRS\LogFiles If you look in this folder it will look something like the following with a bunch of different files with date stamps on the end. In this case because the issue is with a paginated report we need to look in the ReportingServicesService*.log files

What I found in one of the log files when I searched for the subscription Id was the following error

Error processing data driven subscription a743db7f-bbbe-4c45-9da1-2e2e286992dd: Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: The user or group name '<domain>\<contractor user>' is not recognized.

Where the <domain> was our company’s AD domain and <contractor user> was the login that had been used by a short term contractor that had worked with us to build the report in question.

In our case the account in question had been disabled when the contractor left the organization. So obviously nothing was checking the enabled state of this account.

But as it turns out that our AD team had done one of their periodic clean-ups yesterday where they actually deleted a whole group of disabled accounts. So it appeared that this was related somehow to this account no long existing in AD.

We already knew from years of working with Reporting Services that when a subscription is executed that the Owner of the subscription is checked to make sure a valid account is specified. (I believe this is possibly a security measure to stop people setting up a schedule to send reports to somewhere after they have left a company). However we already had the contractor set the subscription owner to one of our service accounts when they created the subscription to try and prevent this very scenario from happening.

In fact searching through all the properties for the subscription in the portal showed no sign of the <contractor user> account anywhere.

At this point I decided to open up a PowerShell window and use one of the tools from the ReportingServicesTools PowerShell module to see if that could shed any more light on this issue.

When I ran the Get-RsSubscription cmdlet I noticed the following:

Sitting in the ModifiedBy property of the subscription object was a reference to our <contractor user> which we were seeing in the error in the log file.

When running Get-RsSubscription against a report where some subscriptions were working and others were stuck in a “Pending” state I could see that the working subscriptions had a ModifiedBy of an account belonging to someone who still had an active account in AD.

My guess as to what is happening here is that Report Server is attempting to populate some of the properties of a user object from Active Directory and is failing now that the users has been physically deleted and this is throwing an exception that is preventing the entire subscription from continuing with it’s execution.

So if you only have a handful of subscriptions stuck in a pending state like this you can just edit them in the portal and make some non-functional change like adding a full stop to the end of the subscription name. This will set the ModifiedBy to your user account and the subscription will start working again.

In our case we took a backup of the ReportServer database and then ran an update statement to set the guid of the ModifiedBy to the guid of our service account user. This is not a supported activity and something you would do at your own risk. But in our case it did allow us to quickly fix numerous “broken” subscriptions that would have taken hours to fix through the UI.

Power BI Report Server needs an Admin Portal

I think one area where Power BI Report Server could do with some more work is in the area of administrator tools. At the moment if a report fails to render because of an error you have to wait for a user to report it. And if a subscription fails to send there is no central place where you can see these issues and easily take steps to correct them.

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.

« Older posts