Category: TMDL View

Connecting Power BI Semantic Models To Data Sources Automatically With Binding Hints

Did you know that you can configure your Power BI semantic model so that it automatically binds to a data source connection when you publish?

To illustrate how to do this, I created an Import mode Power BI semantic model in Power BI Desktop connected to the Products table in the ContosoSales sample database in the Azure Data Explorer help cluster. Anyone can connect to this source, you just need a Microsoft Account to authenticate. Here’s the M code from my semantic model:

let
  Source = AzureDataExplorer.Contents(
    "help", 
    null, 
    null, 
    [
      MaxRows                 = null, 
      MaxSize                 = null, 
      NoTruncate              = null, 
      AdditionalSetStatements = null
    ]
  ), 
  ContosoSales = Source
    {[Name = "ContosoSales"]}
    [Data], 
  Products1 = ContosoSales
    {[Name = "Products"]}
    [Data]
in
  Products1

I then published the model to the Service but of course at that point I couldn’t refresh the model there without the extra step of connecting the newly published model to the source. As you would expect, going to the Settings pane for the semantic model gave me the option to link my data source to a connection in the Service

No surprises so far. I deleted the published semantic model and then did two things.

First I went to the Manage Connections page in the Service and created a new Shareable Cloud Connection for the Azure Data Explorer help cluster. I made a note of the connection ID:

Second, I opened my model in Power BI Desktop, scripted out the semantic model in TMDL View, then added the following Binding Hint to the model:

bindingInfo '{"kind":"AzureDataExplorer","path":"help"}'
    type: dataBindingHint
	connectionId: 42906b42-3e84-461f-aee4-f14fcbeb9b72

Two things to note here:

  • The name of the binding hint is a JSON representation of the connection. It consists of two parts: the kind, which is the type of connection (in this case a connection to Azure Data Explorer) and the path, which is a semi-colon delimited list of all the required parameters of the function used to connect to the source (in this case AzureDataExplorer.Contents). How do you work out what the kind and path are? Originally I worked it out through trial and error, looking at the diagnostic logs and metadata from the functions used to access data, and then I realised all the information was shown in the first screenshot above when the semantic model was not linked to a connection – the kind is shown as extensionDataSourceKind and the path is shown as extensionDataSourcePath. For reference, here’s what the name of a Binding Hint for a Snowflake connection looks like:
{"kind":"Snowflake","path":"xyz.snowflakecomputing.com;COMPUTE_WH"}
  • The connectionId is simply the connection ID from the Shareable Cloud Connection that the semantic model should be linked to.

I then hit Apply and was prompted to upgrade the model to a compatibility level of 1608 (Binding Hints are only available at that compatibility level and above) and clicked Yes:

Having done this, I then republished the semantic model and when I checked the Settings pane it was automatically connected to the Shareable Cloud Connection I had created and could be refreshed immediately:

You can add multiple binding hints if you have multiple connections. You can also add multiple binding hints for the same data source. All in all, this is a nice little feature that might be useful if you are programmatically generating and publishing semantic models and want to avoid an extra API call to bind your model to a data source.

[Update May 2026 – after talking to some of the engineers, I’ve been told that the Fabric list connections API is the best way to get the Kind and Path for a connection]

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.