topic Re: FEATURE PARITY::FABRIC::SEMANTIC MODEL vs PBI DESKTOP SEMANTIC MODEL in Data Engineering

FEATURE PARITY::FABRIC::SEMANTIC MODEL vs PBI DESKTOP SEMANTIC MODEL

Element115 — Tue, 07 May 2024 01:03:53 GMT

ISSUE:

0__click on the following button to create a new semantic model from a user lakehouse:

1__assume you have a DimDate table in there; right-click on the table name to create a calculated column and ... oh wait! no New column menu choice appears as in PBI Desktop:

2__so... given that I have a bunch of calculated columns in a report created with PBI Desktop that I am trying to migrate to Fabric using a lakehouse and DirectLake, how on earth can I create these calc cols?

Except, of course, in M. As long as the code remains trivial, I have no problem. But there's a lot of users out there not fluent in M, and not caring to become fluent in M, and even a bunch of trivial calc cols as these will become a headache for them, not to mention the non-trivial cases.

So what I would like to understand is this: Using Power Query in a DFg2, we can add calculated columns, which then become part of a table that is persisted to a lakehouse. Why then, when opening the semantic model derived from this lakehouse can't we just use the GUI to add some DAX, I mean since we have access to DAX for measures, why can't the user also specify DAX for calculated columns as in PBI Dekstop (and under the hood the engine adds that to the M codebase for this particular query)? I mean, since we can add the M code manually to the DFg2 query to add these calculated columns, why can't we use DAX and have the engine translate that to M under the hood?

It's not like this is the first time that this is done, now is it?

Re: FEATURE PARITY::FABRIC::SEMANTIC MODEL vs PBI DESKTOP SEMANTIC MODEL

Anonymous — Tue, 07 May 2024 06:09:51 GMT

Hi @Element115 ,

Thanks for using Fabric Community.
At this time, we are reaching out to the internal team to get some help on this .
We will update you once we hear back from them.

Re: FEATURE PARITY::FABRIC::SEMANTIC MODEL vs PBI DESKTOP SEMANTIC MODEL

Anonymous — Tue, 07 May 2024 15:48:41 GMT

Hi @Element115 ,

Response from internal team -

Calculated columns aren't supported, so their assessment is correct - they need to perform these calculations further upstream. Whether that be using Power Query M or Notebooks (PySpark / Python, etc.) to materialize the column, that is purely up to the developer to choose the optimal path.

Hope this is helpful.

Re: FEATURE PARITY::FABRIC::SEMANTIC MODEL vs PBI DESKTOP SEMANTIC MODEL

frithjof_v — Tue, 07 May 2024 17:46:48 GMT

Here is an article about migrating an existing semantic model to Direct Lake:

https://github.com/m-kovalsky/fabric_cat_tools?tab=readme-ov-file#direct-lake-migration-1

However, in many cases, import mode semantic models (created in Power BI desktop) will still be a preferred choice (even with Fabric):

https://www.sqlbi.com/blog/marco/2024/04/06/direct-lake-vs-import-mode-in-power-bi/

Re: FEATURE PARITY::FABRIC::SEMANTIC MODEL vs PBI DESKTOP SEMANTIC MODEL

frithjof_v — Tue, 07 May 2024 18:42:18 GMT

If you want the option to use DAX to create calculated columns in Lakehouse tables or Direct Lake semantic models, then you can always create an idea for that:

https://ideas.fabric.microsoft.com/

I would vote for it, as it would be very convenient.

For now, I think creating an import mode semantic model is a good option if you need to create calculated columns in DAX.

And as you say, you could translate DAX code into M (for use in dataflow gen2) or PySpark/SparkSQL (for use in Notebook), if you wish to create the columns in the Lakehouse delta tables.

(A long shot, but maybe you could take advantage of semantic link in a Notebook to use DAX to calculate columns from an existing direct lake semantic model and write the calculated column back to the Lakehouse. But I'm not sure if that would be possible. And if it is possible, then I guess the data would be out of sync for the period between when new data arrives in the Lakehouse to when the semantic link Notebook runs to update the calculated columns. It's just the only way I can think of using DAX in a Notebook today.)

Re: FEATURE PARITY::FABRIC::SEMANTIC MODEL vs PBI DESKTOP SEMANTIC MODEL

Element115 — Fri, 10 May 2024 07:53:33 GMT

Re Import mode, this is not practical when the data set has become so huge that the pbix file explodes in size. There's cost attached to this, as there is cost attached to running a Notebook artifact on top of everything else you might need running. I mention cost because not every company has the money of a Fortune 500 enterpise to afford paying tens of thousands, if not hundreds of thousands a month for the Fabric service. 😉

Finally, M is the more practical solution as it keeps everything together in the same query. What everyone should avoid at all cost is the dreaded ball of yarn, the equivalent of spaghetti code when programming languages still allowed the GOTO statement.

Re: FEATURE PARITY::FABRIC::SEMANTIC MODEL vs PBI DESKTOP SEMANTIC MODEL

Element115 — Tue, 07 May 2024 19:39:13 GMT

Wow! Kovalsky's work is impressive. How did you find this? (Unfortunately, it won't do because of the weird model I have, too long to explain, but it is not straight forward migrating it even with a Jupyter Notebook.)

Re: FEATURE PARITY::FABRIC::SEMANTIC MODEL vs PBI DESKTOP SEMANTIC MODEL

Anonymous — Fri, 10 May 2024 06:48:58 GMT

Hi @Element115 ,

We haven’t heard from you on the last response and was just checking back to see if you got some insights over your query.
Otherwise, will respond back with the more details and we will try to help .

Thanks

Re: FEATURE PARITY::FABRIC::SEMANTIC MODEL vs PBI DESKTOP SEMANTIC MODEL

Anonymous — Mon, 13 May 2024 07:17:38 GMT

Hi @Element115 ,

We haven’t heard from you on the last response and was just checking back to see if you got some insights over your query.
Otherwise, will respond back with the more details and we will try to help .

Thanks

Re: FEATURE PARITY::FABRIC::SEMANTIC MODEL vs PBI DESKTOP SEMANTIC MODEL

Element115 — Mon, 13 May 2024 16:39:34 GMT

I don't understand. There is no feature parity between Power BI Desktop semantic model and the Service/Fabric Semantic Model.

So now we wait until Microsoft implements the missing features or not.