Category: TMDL View

Useful Community Tools And Resources For Power BI And Fabric

There are a lot of really cool free, community-developed tools and resources out there for Power BI and Fabric – so many that it’s easy to miss announcements about them. In this post I thought I’d highlight a few that came out recently and which you might want to check out.

Let’s start with the Fabric Toolbox, a collection of tools, samples, scripts and accelerators created and maintained by some of my colleagues here at Microsoft. The most widely-known tool in there is FUAM (Fabric Unified Admin Monitoring), a solution accelerator for monitoring an enterprise Power BI and Fabric implementations. It’s the successor to Rui Romano’s Power BI monitoring solution, which is now deprecated, but it’s a lot richer than that. It’s already been the subject of a Guy In A Cube video though so I hope you’ve already come across it. There are other things in the Fabric Toolbox that should be more widely known though. My fellow CAT Phil Seamark (why doesn’t he blog anymore???) has been busy: a month ago he announced a new Power BI load testing tool (video here) based on Fabric notebooks which is much easier to configure than the previous load testing tool created by the CAT team. He’s also published a sample MCP Server that, among other things, can analyse a semantic model to see whether it follows best practices. Another colleague, Justin Martin, has published tools for auditing semantic models and DAX performance tuning in the toolbox too. Finally, with the deprecation of Power BI Datamarts looming, if you choose to replace them with Direct Lake semantic models based on Fabric Warehouse (although I think 90% of the Datamarts I’ve seen can be replaced with simple Import models) then there’s a migration accelerator here.

Elsewhere, if you’re a hardcore Power BI developer you’ll already know how useful TMDL View in Power BI Desktop is. Rui Romano recently announced that there’s a new gallery of TMDL scripts where you can see what’s possible with TMDL and share your own scripts. For example, there’s a script here that creates a date dimension table from a Power Query query.

Two years ago I blogged about a tool called PBI Inspector that provides rules-based best practices testing for the Power BI visualisation layer, created by yet another Microsoft colleague, Nat van Gulck. Not only is there now a V2 of PBI Inspector, which will be renamed Fab Inspector, but two weeks ago Nat announced a VS Code extension that allows you to write, debug and run rules from VS Code.

Last of all Gerhard Brueckl recently announced V2 of Fabric Studio, an incredibly powerful VS Code extension that acts as a wrapper for the Power BI/Fabric REST APIs. It lets you browse your workspaces and their contents from VS Code and create/update/delete items among other things; Gilbert Quevauvilliers recently wrote a nice blog post showing how you can use it to download any Power BI report from the Service easily.

That’s enough for now. If there are other tools or resources that came out recently that I didn’t mention, please leave a comment!

TMDL View And Power BI Developer Productivity: An Example Using The Detail Rows Definition Property

For me the biggest new feature in the January 2025 release of Power BI Desktop is the new TMDL View; many other people like Marco are excited about it too. For more advanced Power BI developers (and honestly, I don’t think you need to be that advanced to get value out of it) it makes certain editing tasks for semantic models much simpler, and while I won’t be abandoning the main Power BI Desktop UI completely or stopping using external tools like Tabular Editor it is something I see myself using on a regular basis from now on.

One of the things it allows is the editing of semantic model properties and features that are not exposed by the Power BI Desktop UI but which are nonetheless supported by the engine, and which up to now you’d have had to use Tabular Editor to set. The announcement blog post mentions a few of these – perspectives (useful for the Personalize Visual feature for example) and the isAvailableInMdx property – but my favourite underused property is the Detail Rows Definition property of a measure, also known as Detail Rows Expression. If you have end users querying your model using Analyze In Excel it allows you to customise the results returned by an Excel PivotTable’s Show Details feature; as you might expect Marco and Alberto have an excellent, detailed article on it here. Setting this property allows you to control which columns and rows (so the correct rows are shown for non-trivial measures, as I described here) are returned, and if you can educate your users to use Show Details it can perform a lot better than a gigantic PivotTable to show detail-level data from your model.

What does the workflow for setting this property on a model in Power BI Desktop look like now? What benefits do TMDL View and all the other recent pro developer enhancements in Desktop bring for someone like me? Let’s say I have a measure called Count Of Sales in my semantic model and that I want to customise the columns and their names that are returned by Show Details for this measure. The Detail Rows Definition property takes a DAX expression that returns a table so the first step is to write that expression; now that we have DAX Query View I can do that without leaving Power BI Desktop and because I’m lucky enough to have access to Power BI Copilot (one of the perks of working for Microsoft) I can use that to write my DAX expression easily. I gave Copilot the prompt:

Write a query that gives me the Date, County, Town and Postcode columns columns from the Property Transactions table along with the Count of Sales measure. Rename the Date column to be "Sales Date".

…and it immediately gave me the DAX query I wanted without needing to faff around looking up the syntax to the SELECTCOLUMNS() function:

EVALUATE
  SELECTCOLUMNS(
    'Property Transactions',
    "Sales Date", 'Property Transactions'[Date], // Renaming Date column to Sales Date
    "County", 'Property Transactions'[County],
    "Town", 'Property Transactions'[Town],
    "Postcode", 'Property Transactions'[Postcode],
    "Count of Sales", [Count Of Sales] // Including the Count of Sales measure
  )

Next, I copied the DAX query minus the EVALUATE statement, switched over to the TMDL View pane, dragged and dropped the Count of Sales measure into a Script pane:

And then, underneath the existing measure definition, started typing detailRowsDefinition – the intellisense picked up what I was typing before I even had to finish:

I then tried to paste the DAX query into TMDL View and realised it didn’t like line breaks. Rather than trying to look up how to do this with the editor – which I’m sure is possible – I just switched back to DAX Query View, highlighted the query, entered the prompt:

format this query so there are no line breaks

…and it did the job for me! I copied the DAX table expression again and pasted it into TMDL View after the detailRowsDefinition property:

[I was pleasantly surprised that I didn’t have to escape any characters or mess around with double quotes]

Next I hit the Apply button and tested Analyze in Excel with a PivotTable and Show Details:

And bingo, I got the results I wanted:

Was all of this possible before? Absolutely. Is it much quicker and easier now with TMDL View, DAX Query View and Copilot? Absolutely. I’m a pretty experienced Power BI developer and I could certainly have written the DAX expression without Copilot, I have DAX Studio installed (which has pretty good query builder too) to write and test the query, and I have Tabular Editor to set the property. But being able to do all this just using Power BI Desktop makes me so much more productive.