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
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.
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.
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
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.
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
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.
So a while ago Power BI enabled the ability to display SVG images in tables and matrix visuals. SVG is an XML based language and is actually what the majority of Power BI visual use to render their charts so this technique works really well in Power BI and gives you a way of drawing custom elements in your reports without having to go down the path of building a full blown custom visual. There have been some interesting examples of using this feature such as the sparkline measures created by David Eldersveld (blog) and Reed Haven (blog) and even this funky elephant on hatfullofdata.blog. .
However recently a friend of mine was wanting a way to just build some simple custom data bars with dynamic coloring. So I pulled together an example which produces the following output:
Basically I’m using a text element to output the measure value and drawing a small rectangle under the text calculating the length of the rectangle based of the percentage of the max value. There is also a conditional statement to make amounts less than 50 appear in red.
The code to produce this is relatively simple and I’ve broken it down into a bunch of different variables to hopefully make it easier to understand.
var _barMaxValue = MAXX(all(Sales[Category]), calculate(SUM(Sales[Amount])))
var _barValue = SUM(Sales[Amount])
var _svgWidth = 200
var _svgHeight = 200
var _barHeight = 30
var _barWidth = INT( (_barValue / _barMaxValue) * _svgWidth )
var _fill = IF( _barValue > 50, "blue", "red")
var _svg_font = "normal 100px sans-serif"
var _svg_start = "data:image/svg+xml;utf8,<svg xmlns='http://www.w3.org/2000/svg' viewBox='0 0 " & _svgWidth & " " & _svgHeight & "'>"
var _svg_end = "</svg>"
var _svg_rect = "<rect x='0' y='135' width='" & _barWidth & "' height='" & _barHeight & "' style='fill:" & _fill & ";fill-opacity:0.4' />"
var _svg_text = "<text x='5' y='120' witdh='" & _svgWidth & "' height='" & _svgHeight & "' style='font:" & _svg_font & "'>" & _barValue & "</text>"
_svg_start & _svg_rect & _svg_text & _svg_end
The only “trick” to getting these SVG images to display correctly in the Table and Matrix visuals is to set their Data Category to ImageUrl. If you don’t do this the measure will just display the SVG as text (which could be useful for debugging more complex measures)
If you want to see a working example you can download an example pbix file from my OneDrive.
This was all relatively simple to do since I’ve worked with SVG before so it was not too hard to pull together something simple like this. The biggest problem that I had though was that Power BI restricts ImageUrl’s to only display inside a square, where as to build a nice custom data bar or sparkline using this technique you really want to work in a rectangular space that is 3-4 times wider than it is high.
So I’ve actually added and idea here to ideas.powerbi.com requesting that they change this in Power BI. Please vote for this if you think this would be a good idea.
There was a question recently in the Power BI forums on how to extend the timeout for a connection to Analysis Services used to import data into Power BI. In other tools you can add a setting like “Timeout=600” to the connection string to extend this timeout, but the Analysis Services connector in Power BI does not expose a way to set the raw connection string.
However if you look at the Source step in PowerQuery you’ll see that it calls the AnalysisServices.Database() function and if you check the documentation for this function you will see the following
CommandTimeout : A duration which controls how long the server-side query is allowed to run before it is canceled. The default value is driver-dependent.
So based on the above information my first attempt was to change the call to this function as follows, however this just resulted in an error:
This caused a fair bit of head scratching, but if you re-read the documentation carefully you’ll notice the following “CommandTimeout : A duration …” and a duration is a specific data type in the M language which you can instantiate using the #duration( <days>, <hours>, <minutes>, <seconds>) constructor.
So changing the CommandTimeout option to the following fixes this issue and has the added benefit of being clearer that the timeout is now 5 minutes (when using the connection string parameter its never completely clear what the units of the timeout are)
After many happy years on my previous blog platform I decided that it was time for a move. All my previous posts are still available at http://geekswithblogs.net/darrengosbell but from now on all my new content will be posted on this site. Although geekswithblogs.net had a bright start many years ago it is now suffering from neglect, the rss feed had unresolved issues, the comment engine and contact pages were completely broken and they don’t support https.
So I decided that now was the time to move my blog and give it a facelift in the process.