Can AI write DAX measures? YES! The missing link for this for the longest time has been the the lack of visibility for information about the model. But, with the advent of TMDL, we can now give AI enough context about the model to let it successfully create DAX measures in Power BI.
The “trick” to this is using an AI model that is advanced enough to handle our model context. Many of the older models don’t allow enough text input for this – but the “pro” versions of the common models like Claude and GPT can take it.
They still struggle with the “full” model TMDL code, but the technique is to limit how much you give it at a time. This drastically reduces hallucination.
Desktop AI coding apps vs web browser LLMs
Since we’re copying and pasting code back and forth from Power BI to the AI tool either way, it doesn’t matter so much whether we use the browser like ChatGPT or desktop apps like GitHub Copilot. Both will work, provided it can handle your input size.
On the desktop apps, GitHub Copilot has a tendency to fudge the code up a bit on occasion (e.g. in a ref tablename line, it left out “ref” in its response, despite it being present in the example). I’m not sure if this is an app issue or random chance in the model, but I haven’t had either Cursor or the web browser version of Claude do the same.
I gave a Cursor a try this week too, since it’s a GitHub Copilot competitor that comes up a lot. It seemed to be very similar, but adds on an agentic function that will create whole files for you. I found this to be a bit too much for my use and had to swap it back to chat-only… by too much, I mean that when I asked it to write a single script, it created 5 files – including a documentation file, readme, a .bat executable, and reference file that I didn’t ask for. Like… thanks? I guess?
The ability to create script files like this feels a bit concerning, security-wise. You do have to click “accept”, but given human nature I am betting most people won’t heavily scrutinize the files it creates. It also said Claude 3.7 was “too busy” to use on one of the two days I used it, and suggested that I upgrade if I wanted to be able to use Claude 3.7 reliably, which I thought was weird since it was supposedly a pro trial. I probably will not be renewing it when the trial expires. I can see where actual software engineers could get value out of it, but that’s not quite where I’m at right now.
The desktop apps tend to be more tailored towards editing local files, so presumably this could work with editing PBIP, but I haven’t tried it yet.
Alright, back on topic – here’s the prompt to create our measures in Power BI.
Prompt to create Power BI measures
PROMPT:
Here's my model info - please give me TMDL code to create measures for all the key metrics you that would be useful for this dataset. Do NOT include comments to organize measures into groups. If a measure name has multiple words, use a friendly name with spaces between words surrounded with single quotes. Indentation is important. For decimal measures, use formatString to one decimal. For whole numbers, use formatString to add comma formatting.
Here's an example:
createOrReplace
ref table *Measures
measure Views = SUM(youtube_video_metrics_all[views])
formatString: #,0
Here's my model TMDL info:
<paste specific table TMDL here>Replace the example with a measure TMDL script from your model, removing the lineage tag line. It’s important to remove the lineage tag in your example because if you don’t, it’ll try to generate GUIDs in the code, and that’s something that gets auto-created when the measure is created – so it’ll cause an error.
If you’re not sure where to get the example, we go over the process in the video.
Watch out for…
The AI tends to make assumptions about which table you want the measures created in. Check the code it outputs to see where it’s putting them, and update it if you need to. Also make sure there’s no duplicate measures in your code vs the AI code, because if there’s even one that has the same name it will fail to create the measures.
Also review them to make sure they’re calculating correctly, too. If you’re working with multiple tables, you can include your table relationships by dragging them in as context as well – this will help write any DAX that bridges the tables.
The bottom line
This is pretty great. It works better than I expected, and will only get better as the models improve. I’m waiting on Microsoft to put this directly into the app, though I’m not sure if they will go “all the way” with it, or use it for smaller tasks.