MDX session-scope statements like CREATE MEMBER are not allowed on DirectLake models

[dbeavon3]

I have a pretty simple semantic model with one table pointed at DirectLake (DL on OL).

Most of my tables are "import" tables for the sake of a high-quality pivot table experience in Excel.

When I build pivot tables, I have no problems until I try to create custom calcs or sets.  The Excel U/I allows me to try to create a new calc, and even gives me positive feedback from the "test" button (below) ...

... but when I try to click the OK button I get a real ugly message:

MDX session-scope statements like CREATE MEMBER are not allowed on DirectLake models

As near as I can tell this is the first posting about the error message on the entire internet.  Either people don't use Excel pivot tables very often, or they aren't building semantic models using the new "DirectLake on OneLake" (I think this variety of semantic model is still in "public preview").

Does anyone have a good workaround for allowing users to build their own calculations?  My only recommendation is for them to use power query and ingest everything they need into a local Data Model in Excel.  After doint that, I think they will have no more restrictions when it comes to creating calculations.  Any other tips would be appreciated.

[v-pnaroju-msft]

Hi dbeavon3,

Thank you for the information provided. As mentioned, we will keep the discussion open in the community.

Thank you.

[v-pnaroju-msft]

Hi dbeavon3,

We would like to follow up and check whether the details we shared have helped to resolve your problem.
If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi dbeavon3,

Thank you for your inquiry via the Microsoft Fabric Community Forum.

Based on my understanding, the error indicating that MDX session scoped statements such as CREATE MEMBER are not allowed on Direct Lake models arises because Direct Lake semantic models do not permit session scoped MDX statements (for example, CREATE MEMBER, CREATE SET, etc.). Excel’s Create Calculation or Calculated Member feature issues exactly these session MDX statements, which causes the operation to fail. Microsoft’s Direct Lake documentation explicitly states that session scoped MDX is disallowed, although query scoped MDX WITH clauses remain supported.

Please follow a workaround as below:

1. Add the calculation into the semantic model itself using Power BI Desktop or Fabric by authoring the calculation in DAX. This ensures Excel users see the calculation as a built-in measure, eliminating the need for users to issue CREATE MEMBER statements at runtime and aligning with supported functionality.

For further details, please refer to the link below:
Direct Lake overview - Microsoft Fabric | Microsoft Learn

We hope the information provided helps to resolve the issue. If you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[dbeavon3]

By the way, this language that you shared was added to the docs during a support case I opened:

... however, the explanation for that language wasn't provided.  I didn't talk to any FTE's, only MT engineers.

FYI, At the time of my support case, the error messages popping up in Excel were really strange in that they mentioned "DirectQuery" despite the fact that there was none of that in play.  All they "fixed" as part of my support case was to change the message in the error to refer to Direct Lake.  They didn't actually get Excel back up and running.

Not to state the obvious, but Excel shouldn't let you take 10 steps into a dialog to build an MDX calculation, then give a successful/encouraging reply which clicking the "Test" button, only to fail when clicking the "OK" button.   (This is really not a well-designed U/I and I don't think a junior hobby developer should be satisfied with building it, let alone a 4 trillion dollar company.)

[dbeavon3]

The Direct Lake on OneLake stuff is still in preview, yes?

There is no legitimate technical reason for this restriction, AFAIK.  It is probably an artificial message generated in copy-pasted code that came from the DirectQuery side of things.  In all the docs about Direct Lake on OneLake (DL-on-OL), the PG claims this stuff is basically comparable to "import" models, (after the supplemental framing and transcoding steps have been sorted out).  

I'm hoping that once their stuff comes out of preview, one or two years from now, that they will fix this artificial limitation.  Excel is a very important client-facing tool for end users, and I'm hoping PG will come to see that.

(As a side, I realize that "DirectQuery" models have lots of unusual limitations, but that is a totally unrelated discussion as compared to DL-on-OL.  In that scenario there are multiple query engines involved and the level of additional complexity is quite a lot higher than it is in DL-on-OL.)

Lets keep this discussion open so long as DL-on-OL is still in preview. K?