Finding Power Query Query Execution Times In Power BI And Excel Using SQL Server Profiler

Working out how long a Power Query M query takes to run – the first step to troubleshooting refresh performance problems in Power BI or Excel – is something I have blogged about several times (see here, here and here for example). However, there are problems with all of these methods such as the need to alter the M code of your query. Luckily there is another method that I have found, using a combination of DAX Studio and SQL Server Profiler.

How can you get hold of these tools? You can download DAX Studio for free here:

https://daxstudio.org/

Any serious Power BI developer should have it installed already, but if you have an older version you may need to update it to get the features I talk about in this post.

SQL Server Profiler is part of SQL Server Management Studio, and you can download it for free from here:

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017

You can generate SQL Server Profiler trace files for diagnostic purposes from Power BI Desktop, as Kasper shows here, but these traces don’t show any information about refreshes as far as I can see. You can also hook Profiler up to Power BI Desktop direct, as Sam Lester shows here, but that’s a bit fiddly to do. Luckily DAX Studio now has a preview feature which makes connecting Profiler to Power BI Desktop much easier.

First of all, open up your report in Power BI Desktop, then open up DAX Studio and connect it to the instance of Power BI Desktop you just opened; if you want to use this technique with Excel Power Query queries then you will need to launch DAX Studio from inside Excel. Then, in DAX Studio, open the Options dialog and enable the “Show External Tools” preview feature:

image

Once you have done this, on the Advanced tab on DAX Studio’s ribbon you’ll see an option to launch SQL Profiler and connect it to the Power BI report or Excel file that DAX Studio is currently connected to:

image

Clicking the button opens up Profiler with a trace running. You’ll see that a lot of trace events are selected – probably too many to make sense of – so I suggest that you stop the trace, click the Properties button on the toolbar and then, on the Events Selection tab of the Trace Properties dialog select just the following four trace events:

image

  • Command Begin
  • Command End
  • Progress Report Begin
  • Progress Report End

Restart the trace and then refresh your dataset in Power BI Desktop. You’ll still see a lot of events generated in Profiler, but look for the following:

  • A Command Begin event followed by a lot of Progress Report Begin/End events, which is the command that marks the start of the refresh.
  • After that there will be a series of Progress Report Begin events that have an  EventSubclass of “25 – ExecuteSQL” – one for each of the tables that are being loaded into your dataset and therefore one for each of the Power Query M queries associated with those tables. Don’t be confused by the name of the EventSubclass, these events will appear whatever data source you are using: it looks like the Vertipaq engine requests data from the Power Query engine using a basic form of SQL. For example, here’s a screenshot showing the three events I get when refreshing a dataset with three tables in it called “Property Type”, “Date” and “Price Paid 2017”:
    image
  • Finally, below that there will be a series of Progress Report End events (one for each Progress Report Begin event) with the EventSubclass of “25 – ExecuteSQL”, and the Duration column here will tell you how long it took to get data from the Power Query M query associated with the table. For example, this screenshot shows that it took 5460ms (about 5.5 seconds) to get the data from the “Price Paid 2017” Power Query query:
    image

 

And there you have it, exact timings for each of the Power Query M queries associated with each of the tables in your dataset. Remember that the time taken by each Power Query M query will include the time taken by any other queries that it references, and it does not seem to be possible to find out the amount of time taken by any individual referenced query in Profiler.

There is a lot more interesting information that can be found in this way: for example, dataset refresh performance is not just related to the performance of the Power Query M queries that are used to load data; time is also needed to build all of the structures inside the dataset by the Vertipaq engine once the data has been returned, and Profiler gives you a lot of information on these operations too. Discussion of that will have to wait until a future blog post though…

UPDATE April 2019:

Having used this technique quite a lot in the last few months, I’ve seen that the Progress Report End/25 – Execute SQL event doesn’t always give an accurate value for the amount of time taken to execute an M query – or rather it does, but things are more complicated than I initially understood. You may also need to look at the trace events that come immediately afterwards: Progress Report Begin/17 – Read Data and Progress Report Begin/17 – End Data. Akshai Mirchandani of the SSAS team gave me a great explanation of the significance of these events:

The SQL query traces indicate the begin/end of query execution against the data source – theoretically, once this is done, rows are ready to be pulled from the source.

That’s when you will see the “ReadData” begin event. Rows start getting pulled at that point, until all rows are read (and encoded/compressed) – then you see the end event.

It is not always predictable how each provider/source behaves. Some of them might defer the expensive query execution until the first “Read” operation, but typically the SQL execution step tries to ensure that the command is valid, prepare the command and (at the very least) start the execution of the command.

Sometimes, the command execution might compute all the results and buffer them too.

In effect, its up to the provider/source exactly how this actually behaves…

It looks like the Power Query engine can behave in both of the ways Akshai describes depending on what you’re doing in the query.

Excel.Workbook() And The delayTypes Option In Power Query/Power BI

A while ago I found myself wondering – as I often do about this kind of thing – about what the undocumented third parameter of the Excel.Workbook() M function (called delayTypes) actually does. I found a forums post from Guy Hunkin of the Excel Power Query team in 2017 here, which gives the following summary:

Originally, Excel.Workbook used to read the entire workbook data to accurately assign types to the columns. This was EXTREMELY slow on large workbooks.

Having this in mind, we added this flag to delay the behavior. When set to “true”, we don’t infer any column types. Instead, the UI uses its normal inference algorithm to generate a Table.TransformColumnTypes step with the inferred types and if it inferred wrongly, the user can update the transformation explicitly.

I also posted a question on the Power Query MSDN forum and got some more details from Colin Banfield, one of the many M experts who hang out there, as well as doing some investigations of my own. This post summarises what I found out.

First of all, a simple demonstration of what it does. Consider the following Excel table:

image

Connecting to this table in the December 2018 release of Power BI Desktop generates the following M query:

[sourcecode language=”text” padlinenumbers=”true” highlight=”8″]
let
Source =
Excel.Workbook(
File.Contents(
"C:\DelayTypesTest.xlsx"
),
null,
true
),
SourceData_Table =
Source{[Item="SourceData",Kind="Table"]}[Data],
#"Changed Type" =
Table.TransformColumnTypes(
SourceData_Table,
{
{"TextColumn", type text},
{"IntegerColumn", Int64.Type},
{"DecimalColumn", type number},
{"DateColumn", type date}
}
)
in
#"Changed Type"
[/sourcecode]

Notice that the delayTypes option is now automatically set to true and that there is a “Changed Type” step. The output of this query, as seen in the Power Query Editor, is what you would expect:

image

Compare this with the output of the following query which has delayTypes set to false and has no “Changed Type” step:

[sourcecode language=”text”]
let
Source =
Excel.Workbook(
File.Contents(
"C:\DelayTypesTest.xlsx"
),
null,
false
),
SourceData_Table =
Source{[Item="SourceData",Kind="Table"]}[Data]
in
SourceData_Table
[/sourcecode]

image

There are two things to notice:

  • With delayTypes set to false no “Changed Type” step is necessary, the data types on the columns are set by Excel.Workbook(). With delayTypes set to true all the columns returned by Excel.Workbook() have their data type set to Any and a subsequent “Changed Type” step is necessary to set data types to something useful.
  • With delayTypes set to true the IntegerColumn and the DecimalColumn columns have different data types assigned to them by the “ChangedType” step: IntegerColumn becomes Int64.Type whereas DecimalColumn becomes type number. With delayTypes set to false both columns are set to number. So it looks like Excel.Workbook with delayTypes set to false will only convert columns to primitive data types.

The second question is: if it is, primarily, a performance optimisation how much of an impact does it have? I built four queries that read all the data from an 80MB xlsx file to test the performance of all of the variations of delayTypes true/false and having a “Changed Type” step present or not (just in case there was an overhead to having a “Changed Type” step). Here are the results – the main timings are in minutes and seconds, and in brackets is the length of the “Evaluating” phase of query execution:

delayTypes=false delayTypes=true
no “Changed Type” step 2:08
(1:21)		 1:06
(0:00)
“Changed Type” present 2:19
(1:30)		 1:08
(0:00)

I wouldn’t read too much into these exact timings because in my experience timings for the same query can fluctuate quite a lot, but one thing is clear: setting delayTypes to true results in much better performance. Indeed with delayTypes set to false nearly half the time was spent in the “Evaluating” phase whereas with delayTypes set to true there was no “Evaluating” phase at all. As a result I can’t think of a reason not to set the delayTypes option to true.

It’s worth noting that delayTypes is set to false by default if you don’t specify this option, so be aware of this if you are writing your own M code.

Generating Reports Using Word Documents And Flow

The new “Populate a Microsoft Word template” action in Flow that was released last week got me thinking: apart from the obvious mail-merge applications, this could be used to build reports in Word! Here’s a very simple example.

Let’s say you have a table in an Excel worksheet stored in OneDrive for Business:

image

Let’s say you have a Word document that looks like this:

image

Shown in this screenshot are two Plain Text Content Controls called ProductName and SalesAmount, where text can be entered and with placeholder text already added. The screenshot above is with the document in Design Mode with the controls visible; the document would normally look like this:

image

The idea is to loop through the rows in the Excel table and use the data on each row to populate the content controls in the template and then create a new Word document. Here’s a Flow that does this:

image

Looking at some of these actions in a bit more detail:

  • List rows present in a table gets the data from the Excel table shown above

    image

  • Apply to each loops over each row in the table
  • Populate a Microsoft Word Template takes the Word document shown above and then puts the value from the Product column in the current row from Excel into the ProductName content control, and the value from the Sales column in the current row into the SalesAmount content control:

    image

  • Create file saves the resulting new Word document in a separate folder:

    image

Here are the three Word files that are created:

image

And here’s what the contents of AppleSales.docx looks like:

image

This is only a very, very basic example of what’s possible: you could convert these documents to pdf, email them out as attachments, and even in the future get your source data direct from Power BI.

Why would you want to build reports in Word though? Some reports are very text heavy, and although you can do text substitution in Power BI (for example using the Enlighten Data Story visual), nothing does highly-formatted text as well as Word. OK, it’s not going to be something that you do very often – the vast majority of reports you build will be better off in Power BI, as paginated reports/SSRS, or in Excel – but there are definitely some use cases. The new Flow action is very limited right now but with support for more types of content control even more sophisticated reports will be possible using this technique.

Power BI Roadmap Announcements In The April 2019 Release Notes

The latest version of the “release notes” document that details the roadmap for Dynamics 365 and the whole Power Platform – which includes Power BI – has just been released. You can view it online here:

https://docs.microsoft.com/en-us/business-applications-release-notes/april19/

There are lots of very exciting announcements for Power BI. Here are a few of the features that are promised for later this year that caught my eye, that haven’t already been announced elsewhere (although some of them were discussed in public sessions at the last PASS Summit):

  • Expression-based formatting in Power BI reports – the ability to use a DAX expression to control properties such as colour and line style within a visual. One of those features that advanced Power BI users will love, and which will allow all kinds of interesting customisations.
  • Performance analyzer for reports – a tool in Power BI Desktop to tell you how long each visual on a page takes to refresh, making it easier to understand where your performance bottlenecks are. I wish we could have something similar for Power Query queries…
  • Customised navigation through apps
  • Shared and certified datasets – this is going to be incredibly useful! As the docs say “With shared datasets in Power BI, we are allowing a single dataset to be used by multiple reports, across workspaces”. I can’t wait. Lots of other cool, related features too here.
  • Deeper integration of paginated reports in Power BI – including the ability to create paginated reports on top of Power BI datasets.
  • Large-scale compute engine for dataflows – this is somewhat cryptic. The docs say “We are enhancing the dataflow calculation engine in Power BI Premium to enable high-performing large-scale data transformations (TBs of data).”. Are these enhancements to the Power Query engine, or is it being replaced with something else entirely (eg to make it more like Azure Data Factory?). I don’t know.
  • New dataflow viewer to visualize dataflow dependencies – I guess something similar to what we have in the “Query Dependencies” view in the Power Query Editor?
  • More new Q&A features: auto-suggestions and did you mean. I’m glad Microsoft is continuing to invest in Q&A, I think it’s going to be one of those features that suddenly goes from being a demo feature that few people actually use to something that will be very powerful indeed. In fact, I would argue that it’s already a lot more powerful than most people realise.
  • Python support in the Power BI service
  • Power BI Template Apps – the ability to create multiple apps in different workspaces from a single template. This was something that used to be possible with content packs (remember them?) so I guess anyone still using them should be able to finally migrate away. Useful for end-user customisation of apps.
  • Better integration between Flow and Power BI, for example integrated alert creation, and the ability to query a dataset and trigger refresh of a dataset from Flow. This last feature will be really important – there are lots of scenarios where you only want to refresh a dataset when you know the data is ready.

The Is Nullable Column Property In Power BI

The new Relationships view (or Modeling view – it seems to have two names) in Power BI Desktop that has been in preview since November 2018 not only makes it easier to work with complex models and set properties more easily, it also exposes a brand new property on a column: the “Is nullable” property. It’s visible at the bottom of the new Properties pane when you click on a column:

image

I was, naturally, curious about what it did and I couldn’t find any documentation so I did a bit of investigation of my own and asked a few people at Microsoft.

It turns out that it is primarily intended for validation purposes, so that if you know a column should never contain a null value and then, at a later date, a null value does appear in that column then you’ll get the following error when you try to refresh a table in Import mode:

image

Column ‘MyColumn’ in Table ‘TestTable’ contains blank values and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table

The only other use I have found for it is when it is used in combination with the DAX CombineValues() function in DirectQuery mode. In his excellent article on this subject, Marco notes that the SQL generated when you use CombineValues() to concatenate values from multiple columns and create a calculated column that can be used in a relationship includes a check for null values in those columns. I can confirm that if you turn off the ‘is nullable’ property on all the columns used by CombineValues(), there are no checks for null values in the SQL queries.

There may be a few other things that it does, but I’ve been told they are likely to be there to ensure correctness rather than performance optimisations. Nonetheless if you do find other scenarios where it has an effect please let me know in a comment.

[Thanks to Akshai Mirchandani and Jeffrey Wang for providing much of the information used in this post]

Power BI Data Privacy Levels And Cloud /Web-Based Data Sources Or Dataflows

UPDATE: it is now possible to set privacy levels for cloud data sources in the Power BI portal. See https://powerbi.microsoft.com/en-us/blog/privacy-levels-for-cloud-data-sources/

I have already blogged in great detail many times about Power BI/Power Query data privacy settings (see this series for example) but there’s always something new to learn. Recently I was asked a question by Ian Eckert about how Power BI handles data privacy for cloud or web-based data sources after a dataset has been published, and it prompted yet more revelations…

Consider the following M query:

[sourcecode language=’text’ padlinenumbers=’true’]
let
Source = Xml.Tables(
Web.Contents(“https://blog.crossjoin.co.uk/feed/”)
),
channel = Source{0}[channel],
language = channel{0}[language],
out = Json.Document(
Web.Contents(
“https://data.gov.uk/api”,
[
RelativePath=”3/action/package_search”,
Query=[q=language]
]
)
),
result = out[result],
results = result[results],
#”Converted to Table” = Table.FromList(
results,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
Column1 = #”Converted to Table”{0}[Column1],
#”Converted to Table1″ = Record.ToTable(Column1)
in
#”Converted to Table1″
[/sourcecode]

It doesn’t do anything particularly interesting, but it does take data from one web-based data source (the RSS feed for this blog) and sends it to another (the UK government’s open data metadata search web service). As a result, in Power BI Desktop, if you set the data privacy settings for both data sources to Public then the query runs, but if you set the data privacy settings for both data sources to Private:

image

image

…As expected, you get the following error:

image

Formula.Firewall: Query ‘Test’ (step ‘Converted to Table1’) is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.

Now the strange thing is that, when you publish the dataset that contains this M query, refresh always works. Why? What’s more, other datasets that do something similar will always fail when refreshed.

It turns out that when you publish a dataset that uses cloud or web-based data sources like the two used here, the Power BI service does not use the data privacy settings you have set in Power BI Desktop but instead it automatically assigns data privacy levels as follows:

  • Data sources, like the ones used here, that use Anonymous authentication are automatically given the privacy level Public
  • All other data sources are given the privacy level Private.

Interestingly, Power BI dataflows also count as cloud-based data sources and because they do not use Anonymous authentication they default to Private too, so if you are combining data from a dataflow with another data source in your dataset then you need to be careful of this.

What’s more there is at the time of writing no way to change these data privacy levels in the Power BI web-based portal. Hopefully this will change soon.

There are some workarounds though!

First of all, you can force refresh to take place through a gateway. This might sound strange because in theory, if you’re only using cloud or web-based data sources, a gateway should not be necessary. However there are already similar scenarios where a gateway is needed, for example if you are scraping data from a web page you need to use a gateway, and if you are combining data from a cloud-based data source with an on-premises data source you also need to use a gateway. If you add your cloud/web-based data sources as data sources in your gateway (unfortunately it does not seem to be possible to add a dataflow as a data source in a gateway, though) you can set their data privacy levels in the Advanced Settings section in the Manage Gateways screen:

image

You will also need to set the “Use a data gateway” option to On in the Settings dialog for your dataset after it has been published:

image

The other workaround is to copy the M code for your query and paste it into a new blank M query in an entity in a dataflow, as Matthew Roche shows here. While it does not seem to be possible to set data privacy levels for individual data sources when creating an entity, it is possible to turn off data privacy checks for an entity completely. If you create a query that sends data from one data source to another (regardless, as far as I can see, of the authentication mechanism used), you will see the following message in the Power Query Online query editor:

image

The evaluation was cancelled because combining data from multiple sources may reveal data from one source to another. Click Continue if the possibility of revealing data is okay.

If you click Continue, data privacy checks are turned off and the query runs; you can also click the Options button on the ribbon and check the “Allow combining data from multiple sources” option:

image

If one of your data sources is already itself a dataflow you may need to do some editing of the M query to make things work, but as Matthew Roche shows here it is possible to have an entity in a dataflow refer to another entity without using a computed entity (which is a Premium-only feature).

[Thanks to Arthi Ramasubramanian Iyer from Microsoft for providing background information for this post]

Power BI/Power Query/M Pre-cons At SQLKonferenz, SQLBits and SQL Saturday Iceland

If you’re learning Power BI and you want to deepen your understanding of its data transformation and loading functionality, the Power Query engine and the M language, I’m doing one-day pre-conference seminars on this subject at three conferences over the next few months:

You’ll learn about all the functionality in the Power Query Editor window plus concepts such as merging, appending, parameters, functions, data privacy and M. I hope to see you at one of them!

Updated Power BI Video Training Course Now Available

I’m pleased to announce that the updated version of my Power BI video training course is now available via my friends at Project Botticelli. You can find out more and purchase it here:

https://projectbotticelli.com/power-bi-desktop-and-cloud-course-video-tutorial?pk_campaign=cwblog2019&code=crossjoin

If you book using the discount code CROSSJOIN you’ll get 10% off!

I’ve recorded a lot of new content to cover all the new features that were added to Power BI in 2018, and there are also new segments on Azure Analysis Services and Power BI Premium. You can watch a few of the videos for free to get a taste of the course.

Don’t forget that my older MDX and SSAS Multidimensional courses are also still available on the same site.

ODBC Data Sources, The SqlCapabilities Option And Power Query/Power BI Data Refresh Performance

A few weeks ago I received a great tip from Simon Nuss about a Power Query feature relating to ODBC data sources that I hadn’t seen before (although it’s in Power BI Desktop and Excel 365, so it can’t be that new) and which is potentially important for the performance of data refresh and of the Power Query Editor’s preview pane. It’s only visible if you click “Advanced options” when creating your ODBC data source:

image

It’s the “Supported row reduction clauses” option:

image

What does it do? Well, first of all let’s see what happens if you don’t set it. Let’s say you have an ODBC data source that uses the Microsoft ODBC Driver for SQL Server to connect to the Adventure Works DW database. If you create a new query that uses this data source to get the contents of the DimDate table it works pretty much as you’d expect; here’s the M code:

let
Source =
Odbc.DataSource(
“dsn=Test SQL”,
[HierarchicalNavigation=true]
),
#”Adventure Works DW_Database” =
Source{
[Name=”Adventure Works DW”,Kind=”Database”]
}[Data],
dbo_Schema =
#”Adventure Works DW_Database”{
[Name=”dbo”,Kind=”Schema”]
}[Data],
DimDate_Table =
dbo_Schema{[Name=”DimDate”,Kind=”Table”]}[Data]
in
DimDate_Table

image

However, if you look in Profiler when the contents of the Preview window is refreshed, you’ll see that the Power Query engine is running a query that gets the entire contents of the table:

image

Obviously, if you are connecting to a big table this could make the Power Query Editor slow. What’s more, as this blog post shows, the Power Query Editor only actually wants to display 1000 rows and the fact that the query is requesting all the rows from the table suggests that even basic query folding is not happening on this ODBC data source.

Now let’s see what happens if you do select something from the “Supported row reduction clauses” dropdown. As the screenshot above shows, there are four values in the dropdown that relate to the form of SQL supported by your ODBC source to only get the first N rows from a table. If you don’t know which one to choose you can click the Detect button and it will select one for you. In this case I selected TOP:

image

Having done this, when you connect to the same DimDate table, you’ll see that now the Power Query Editor is only requesting the top 1000 rows from the table:

image

Also, if you look at the M code for the query, the Odbc.DataSource function has the as-yet undocumented SqlCapabilities option set in its second parameter,

Odbc.DataSource(
“dsn=Test SQL”,
[HierarchicalNavigation=true,
SqlCapabilities=[LimitClauseKind=LimitClauseKind.Top]
]
)

Actually it’s not quite undocumented because it is mentioned here as something that is only available if you’re using Odbc.DataSource in a custom connector; obviously things have changed.

Setting this option can make a big difference to the responsiveness of the Power Query Editor when you’re working with large tables and/or slow data sources – I saw this myself this week when working with one of my customers. It’s also going to be important if you use the Keep Rows/Keep Top Rows button on the ribbon or the Table.FirstN() M function; I suspect there are other, more obscure, reasons why it might speed refresh up even if you aren’t filtering the rows of the table but I’m not 100% sure.

One last thing to mention is that there is another undocumented function which is what, I think, the Detect button in the screenshots above uses to find out how to set the SqlCapabilities option. It’s called Odbc.InferOptions and it can be used like so:

Odbc.InferOptions(“dsn=Test SQL”)

This function returns a record containing a field called LimitClauseKind that tells you the type of limit clause that is supported:

image

image

Here’s a list of the possible LimitClauseKind values I found using #shared:

image

Fourteenth Blog Birthday

Every year, on the anniversary of the first-ever post on this blog, I write a post summarising my thoughts on what’s happening in the world of Microsoft BI and what I’m up to professionally.

This year, rather than go on about how Power BI is taking over the world (which we all know already), I thought it might be interesting to consider how the focus of this blog – and by implication the focus of my work – has changed over the last few years by looking at the viewing statistics of some of my most popular posts.

As you probably know, for a long time the only product I cared about was Analysis Services Multidimensional and MDX: it was pretty much all I blogged about and the only thing I did consultancy and training on. The following graph shows how the number of hits on four of the most-viewed posts on this subject from 2014 to 2018: Aggregating the result of an MDX calculation using scoped assignments; Joining the results of two MDX queries together; Last Ever Non Empty – a new, fast MDX approach; and One Cube vs Multiple Cubes.

image

 

None of these posts are, in technical terms, out of date but the downward trend is the same for all of them. The decline in traffic is matched by the decline in demand for consultancy and training on SSAS MD and MDX. While I still spend around 20% of my time doing SSAS MD and MDX consultancy, I do very little training on them at all these days – I guess because no-one is building new solutions using SSAS MD, although there are a still a large number of SSAS MD solutions in production that need maintenance and improvement. I expect the launch SSAS MD in the cloud as part of Power BI Premium will lead to a spike in the amount of work I do on it as I help my customers migrate but that will only be short-lived.

In contrast, look at the trend for four of my most-popular Power Query/M related posts: Referencing individual cell values from tables in Power Query;  Working with web services in Power Query; Creating tables in Power BI/Power Query M code using #table(); and Web.Contents(), M functions and dataset refresh errors in Power BI. These are not necessarily new posts (the earliest dates from 2014) but again they are all still technically relevant and the steep increase in the amount of hits over the last few years that they receive is clear:

image

Power Query and M is a bit of a niche topic, though; right now my most popular posts are on general Power BI data modelling and DAX – a topic I don’t actually blog about all that often, but which I nevertheless spend a lot of consultancy and training time on. The following graph shows the trends for the posts Comments and descriptions in DAX; Creating current day, week, month and year reports in Power BI using bi-directional cross-filtering and M; Dynamic chart titles in Power BI; and (although I’ve never really understood the popularity of this one) Using DateDiff() to calculate time intervals in DAX.

image

Perhaps I should blog about this more? The reason I don’t is twofold: first, there are a lot of people out there such as Marco and Alberto who specialise in DAX, have covered all the obvious angles and do a much better job than I ever could; second, my philosophy has always to blog about what I’m interested in and excited about, and frankly I have always enjoyed Power Query and M more than DAX.

One last graph is needed for context, showing the most popular posts from the three graphs above next to each other. The following graph shows how Aggregating the result of an MDX calculation using scoped assignments, Working with web services in Power Query and Dynamic chart titles in Power BI compare against each other:

image

It goes to show how the “Dynamic chart titles” post is now much more popular that the “Aggregating the result of an MDX calculation” post was, even at the peak of its popularity. I guess Power BI is a safe bet for my future.