Create calculated column while live connecting to power bi datasets.

[Anonymous]

Consider this scenario:

Sales/Inventory data contains millions and millions of rows, alll uploaded to service and refreshed every day.

A user wants to create a new column, for some ad hoc reporting reason. He makes a live connection to the power bi dataset and finds out that he cannot create a simple calculated column but only measures.

Are we expecting from a business user to download the original pbix (about 500mb), create the column and then reupload it? Even with dataflows, the data resides in the memory .

What am I missing?

Isn't it a big obstacle in creating a datadriven/ self-service bi culture using power bi?

If there is some other way I have not considered?

[Seth_C_Bauer]

@Anonymous I've found myself wanting to do the same thing in these scenarios but I don't think it will change. Calculated Columns are significantly different in that they only refresh when the model is processed and the tables refresh. Measures are "in model" constructs. Thus, we have the ability to create report side in memory calculations with measures, but since calculated columns don't behave the same way, nor refresh without a model refresh, I can't see this changing...

The only suggestions I'd have would be to try to use "X" functions to produce row by row calc measures, or store the main PBIX in a shared location so that people don't have to download the PBIX every time and can republish changes to the overall model if necessary.

[Anonymous]

@Seth_C_Bauer  thanks for the reply.

The only alternative solution to what you have proposed that I have thought of, requires a big change in our organization's data architecture (we are on prem - oracle dw).

If the report is in direct query mode, then it is very simple for the business user to download a small pbix report (or maybe on a shared file) create the calculated columns and then reupload it in a few seconds ( since the data does not reside in the pbix file).

From the performance view though this requires that the data are "cached" so we need azure analysis services.

Do you find this approach good?

[Seth_C_Bauer · Accepted solution]

@Anonymous Couple alternatives, and a clarification.

First, there are 3 types of connections (Import, Direct Query, Live), there is a lot of confusion around this because many people keep calling the live connection "Direct Query" and it isn't because there are different behaviours.

Direct Query is a connection to your database, and will be the slowest performing of all the types. I rarely recommend it due to that fact. The reason is that it will constantly be shooting queries to you database (which also means a higher volume of traffic and calls to the database as more people use it).

It does not require SSAS. The model part still lives in Power BI, which is why you would have flexibility to create calculated columns.

Live Connection - where you would need AS, is probably the least viable option because you have no visibility into the model, and you can't create calc columns or do much of anything besides create a measure.

You may know this, but if not I wrote up a series of blogs awhile ago describing the pros/cons of all the connections. https://powerbi.tips/?s=connection

The other alternative would be a route where you have your model file in the PBIX deployed to a SharePoint (OneDrive) folder. Users can sync to this folder and easily interact with the report via this folder without waiting for a download. The trick here, is that you can connect Power BI to the OneDrive location and the PBIX, and the report auto-saves, or you can just initiate a refresh of the dataset to pull the new calc column into the shared dataset. This is slightly confusing, but my buddy Mike wrote up a blog explaining how Power BI works in this scenario. It actually is a really great way to keep your reports centralized and make changes without publishing all the time from random people's machines. He starts talking about what I'm describing right after creating the workspace, about half way down -> https://powerbi.tips/2017/08/best-practices-for-sharing/

Hope that helps a bit