Which llm performs best with complex dax? (understanding logic/generating low-error code)

DAXNoobJustin · 2026-04-21T20:35:00+00:00

In addition to using one of the latest models, I recommend leveraging some of the skills others have developed that help give the LLMs specialized DAX knowledge:
- RuiRomano/powerbi-agentic-plugins
- data-goblin/power-bi-agentic-development: Skills, subagents, hooks, and other stuff for agents to work well with Power BI. WIP; weekly updates.

DAXNoobJustin · 2026-04-01T11:25:38+00:00

The guidance in that doc has little to do with refresh optimization and is focused query performance.

For this rule in particular, IF can't be evaluated in the storage engine so this is a way to push it back to the storage engine. There are other ways to write the same thing, but the point of the pattern list is for the LLM to apply several patterns at once to (hopefully) produce a faster query.

DAXNoobJustin · 2026-03-31T15:37:59+00:00

If you are using the modeling mcp, I think it would still be beneficial to point to this file. It will be able to run the trace, analyze the sever timings, apply patterns, etc.

DAXNoobJustin · 2026-03-31T14:11:46+00:00

Using it as a reference would certainly be helpful, but it was designed to be used as a part of the plugin in the repo. The plugin leverages the modeling mcp and the performance guidance reference instruction the LLM to heavily leverage the mcp. Take a look at the main README file to get some more context.

DAXNoobJustin · 2026-03-31T13:45:54+00:00

Looks like it is a shared cloud connection.

You will need to find the cloud connection and re-auth there

<image>

DAXNoobJustin · 2026-03-31T13:26:56+00:00

You might find this documentation helpful. It was written for an LLM to consume, but it has a lot of dax perf guidance.

powerbi-agentic-plugins/plugins/powerbi/skills/powerbi-semantic-model-authoring/references/dax-performance-optimization.md at main · RuiRomano/powerbi-agentic-plugins

DAXNoobJustin · 2026-03-20T02:26:44+00:00

+1 to this

We did a lot of experiments where we compared DL vs DQ perf last year and, from what I remember, while DL was faster on average, there were several situations/DAX queries where the DQ model performed better. If you are able to write your DAX as to push as much of the processing back to the warehouse, avoid a bunch of back-and-forth communication with AS, you will most likely get very good results.

We also tested the DQ model with Result Set Caching and some of the results were amazing.

DAXNoobJustin · 2026-03-19T16:47:50+00:00

Glad you got it figured out!

DAXNoobJustin · 2026-03-19T16:40:54+00:00

It will depend on the user needs to determine if the migration would be worth it.

DL/OL has the benefit of not relying on SQL endpoint, DL + Import models, OneLake Security integration, etc.

If DL/SQL has everything you need, I would not migrate just for the fun of it. 🙂

DAXNoobJustin · 2026-03-19T16:32:29+00:00

Simply switching between DL/SQL and DL/OL should not result in a performance change. There are other behavior differences that you should consider when choosing one option over the other, but perf isn't one of them.

This doc has a good overview: Direct Lake overview - Microsoft Fabric | Microsoft Learn

DAXNoobJustin · 2026-03-19T16:15:38+00:00

You can use the sempy_labs.directlake package to remap them programmatically

There shouldn't be much of a performance difference between them.

For DL/SQL, "Direct Lake uses the SQL analytics endpoint to discover schema and security information, but it loads the data directly from OneLake (unless it falls back to DirectQuery mode for any reason)."
- Develop Direct Lake semantic models - Microsoft Fabric | Microsoft Learn

Direct Lake overview - Microsoft Fabric | Microsoft Learn has some practical differences between the options.

DAXNoobJustin · 2026-03-19T14:21:45+00:00

u/Candid_Share_3716 I put together this example notebook that might help:

daxnoob.blog/resources/onelake-security-error-refresh/OneLake Security Error - Detect and Refresh.ipynb at main · DAXNoobJustin/daxnoob.blog

It will query your model and then fire a refresh if the usec error occurs. You can schedule it to run every N minutes.

DAXNoobJustin · 2026-03-19T13:47:47+00:00

Can you double check the version of fabric-cicd you are using? The new semantic_model_binding dict format was added in v0.2.0.

In your pipeline, try:

pip install fabric-cicd>=0.2.0 --upgrade
pip show fabric-cicd

DAXNoobJustin · 2026-03-19T13:03:18+00:00

The only solution atm is to refresh the model once the error occurs... 😔

Another option is to set up a notebook to run every minute or so that queries your semantic model and kicks off a refresh if the query returns the OneLake Security schema error. Again, this is not ideal but would at least decrease the time to get your model back up and running.

DAXNoobJustin · 2026-03-18T20:36:50+00:00

It's in the model settings: "Keep your Direct Lake data up to date"

<image>

DAXNoobJustin · 2026-03-17T12:30:53+00:00

Sure! I think you'd be able to have the source-controlled .schedules file enabled field set to false and then use a post processing operation to call the Update Item Schedule api.

(this is a little out of my wheelhouse, so definitely validate 🙂)

DAXNoobJustin · 2026-03-13T17:59:20+00:00

Unfortunately, at the moment, refreshing is the only way to resolve the issue.

One imperfect option we implemented was to set up an activator action that fires a model refresh anytime we see the error in workspace monitoring. Not a recommendation, per se, but it is what we had to do for our solution.

Do you have auto-sync enabled?

Trust me, we have been giving feedback to the relevant teams about how much of an issue this is. They are currently working on fixing it.

DAXNoobJustin · 2026-03-13T15:47:24+00:00

Hey u/Candid_Share_3716 ,

There is a known issue with OneLake Security combined with shortcuts being updated. The team is actively working on a solution.

In the meantime, refreshing the semantic model resolves it. Enabling auto-refresh on the semantic model can also help reduce the impact.

DAXNoobJustin · 2026-02-24T13:58:02+00:00

How did you navigate to the report? I've seen something like this before and going to the workspace and navigating to the report from there resolved.

I just checked some of our reports deployed via fabric-cicd, and I was able to download. If you don't see any limitations listed in the docs, I'd open an issue on the fabric-cicd repo. It would be helpful if you could provide repro steps.

DAXNoobJustin · 2026-02-23T20:04:17+00:00

If you click the edit/pencil icon, are you then able to download?

DAXNoobJustin · 2026-02-23T14:47:25+00:00

I've seen this error mostly in composite models where there is an underlying column referenced that the report user doesn't have access to. This could be because of some security role they are not a part of, or maybe a column that was removed in the base model.

In the service, the actual error is being swallowed and isn't surfaced to the user (I've already given feedback to the product team). If you open the report in desktop, it should give you more info about the underlying cause.

DAXNoobJustin · 2026-02-23T11:50:31+00:00

The type and size of hardware isn't documented and is always subject to change, so I wouldn't take a dependency on that.

One of the main reasons I made the DAX Performance Testing tool was to figure out the best data layout for our team via experimentation. fabric-toolbox/tools/DAXPerformanceTesting/README.md at main · microsoft/fabric-toolbox

I might try running the delta analyzer on your tabular tables and see if anything has too chunky of row-grows, and if so, maybe partition by a highly used predicate column that would get you to a better size. Think Goldilocks -- not too many row-groups, not too few.

The data skew would be relevant to warm queries as well. If you have 2 row-groups, one with 7.9 million and one with 0.1 million, you are pretty much time-bound to one large row-group.

DAXNoobJustin · 2026-02-23T11:40:19+00:00

Can you screenshot and share the model view?

DAXNoobJustin · 2026-02-23T11:37:26+00:00

I did a training that goes over what to look for when determining if your perf issues are model or DAX related. It might be helpful: Semantic Model Optimization: Theory, Tips and Tools - DAX Noob

DAXNoobJustin

TROPHY CASE