Datamart question

[rdnguyen]

The calculated column is proven to be useful and convenient in report design which is absent in live connection report hooking up to Datamart; I wonder how would you deal with this when utilizing datamart as self-service model?

[v-cazheng-msft · Accepted solution]

Hi @rdnguyen ,

These kind of limitation is determined by the connection mode Live Connection itself. When the connection mode is Live Connection, model actions like build relationships, create calculations etc. are not supported in Power BI for the data source is a tabular model that can be used by Power BI directly. For such kind of data source, calculations like create Calculated columns, measures are completed in the data source. When you live connect to Datamarts, these limitations are applied as well. Fortunately, Power BI provides the ability to change the connection mode of Datamarts from Live Connection to Direct Query if you would like to calculate on connected model. With the Direct Query mode, you could build your Calculated columns and relationships.

Before the switch, you need enable DirectQuery for PBI datasets and AS in the Preview features and restart your Power BI Desktop.

Click on Make changes to this model

Then, the connection mode is Direct Query. You could create Calculated column or build relationships in this environment.

However, there still some considerations when creating Calculated columns with Direct Query mode. For more details, you could refer to Use DirectQuery in Power BI Desktop - Power BI | Microsoft Docs.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[itchyeyeballs]

I stopped using calculated columns as I found the initial benefit of being able to quickly create them was more than offset by the extra work figuring out where the logic was when I returned to the model after a period of time and couldn't remember if they were in the ETL or model. It was also easier to ask my team to do everything in ETL so it was always in one place.

[rdnguyen]

Another question regarding the relationship model, why cannot I build many-many relationship in datamart; Should we always make extra table to set up 1-many relationship for every many-many relationship?

[otravers]

Using a factless fact table as a bridge to avoid many:many relationships has always been the official recommendation anyway, so I don't think it's a big loss.

[itchyeyeballs]

I guess the theory is that a datamart enables users to modify the ETL process directly so you don't need calculated columns in the model?

[rdnguyen]

@itchyeyeballs I would likely agree that calculated column could be done perfectly at Power Query editing level, but the calculated column is more convenient on the fly, when you want to display combined data as slicer options. Regarding combined data for slicer options, how would you deal with that?