The PBI file gets extremely slow after creating a few calculated columns.

[Arklur]

I have 4 tables in my dataset, one of them has about 150k rows, the other ones containt just a few thousands. I've noticed that after I create a few calculated columns on one of the tables, the file gets extremely slow, and I'm not talking about the refresh. Just clicking on the "Add New Column" takes 1 minute or so to get the formula bar to appear, but even removing it (clicking on the "X") takes also a lot of time, or deleting a relationship between the tables, etc.

Any idea why does this happen? I know my column is quite complex, but...why does it make the whole file slow, no matter what I'm doing in it?

[cpb]

I have this problem - it was taking me 10 minutes to add a new measure - split equally between the wait for the measure to be ready to type the formula, the wait once the formula had been entered and then the wait if I wanted to change its format. Very tedious if you have 10 new measures to add for a new report This is in a nearly 1Gb model with c2,000 measures.

However, I read that I could save some space by turning off "Auto Date/Time" in the options for the model and as well as saving c100Mb it has now that has reduced that 10 mins to 3 mins. 

[v-ljerr-msft]

Hi @Arklur,

After a few research, I still cannot find a good explanation on this issue. However, I do find there are some others also encountered the same issue. Following is a comment of @KHorseman from this similar thread:

I have to build all my reports on a server outside our network that I have to remote into. I don't know its specs but they are definitely not great.

That said, I still don't understand why adding measures is what slows it down. I would think actually calculating the results of those measures would be the slow part, but it's not. I can add put one of my crazy nested double-SUMX measures into a visual and it loads up in two seconds at worst. But when I hit New Measure, before I even type a single character of the actual formula, I have to wait five minutes between hitting that button and being able to start typing. Then when I'm done I have another five mintue wait.

I don't get what it's doing in that time. It's not calculating anything, because I haven't even given it a formula to calculate yet. It's not rendering any visual. I could maybe understand a slight delay after entering the formula while it checks to make sure it's valid and contains the right context transformations and such, but before there's even a formula to check? That makes no sense. From seeing this kind of behavior in other programs, this really seems like some kind of memory leak bug to me.

---

@KHorseman, do you still have this issue? If you have some workaround on it, could you share it with us?

Regards

[KHorseman]

@v-ljerr-msftmy workaround is usually to do a major cleanup project on my data model. My long term solution will be to build a data warehouse...one of these days.

In my experience it seems like it's caused by the number of measures in the file. That is the only consistent thing I can see that appears to relate to the gradual slowdown of a pbix file. The first 40 or so are free, and after that each new one takes a little bit longer to add than the last. I don't know if that's related to @Arklur's issue here or not. I usually try to keep DAX calculated columns to a minimum, maybe a dozen in a fairly complex data model if I haven't gotten lazy. If they are the cause of this, then the threshold is pretty low. Data models that had much more than a dozen calculated columns in the past also had over 100 measures so it would be hard to tell which one (if either) was related to the slowdown.

[Anonymous]

@KHorseman  Good insights here, the calculated columns were certainly the main cause of slow-down for my PBIX file. Similar to you, I had about a dozen, but in my model I had only about 15 measures. Removing the calculated columns greatly sped up the "create new measure" experience in the PBI desktop. Doing the opposite, and remvoing the measures, did not change the slowness of "create new measure". This leads me to believe that the calculated columns were for sure what was causing the slow downs, and it makes most sense to find other ways to get around calculated columns (mostly pushing the logic back to the Query Editor or the data source).

[dieterdecaluwe]

It's so annoying. Does anyone know of any way to add dax measure to the .pbix file without having to open it? Because waiting a full minute just for the input bar to become responsive, put the formula, wait another 2 minutes for it to be "processed". 3 or more minutes to add 1 formula is ridiculous.

The input bar for new formulas is also very finicky, like try deleting part of your formulas content with backspace or delete. Even selecting part of text (or entire) often doesn't work, neither does copy pasting work half the time.

An absolute abomination the way it works right now.

[Anonymous]

@dieterdecaluweUsing Tabular Editor allows you to add measures directly into the SAAS model for the PBI file. That seems to be an efficient method of skirting the "new measure" from within the PBI desktop file. Based on my recent findings, calculated columns, can really clog the model. I had about a dozen, and didn't realize how slow they were making the GUI interface. I'd recommined giving this a shot: remove all (or most) of the calculated columns, and see if performance improves.

[v-ljerr-msft]

Hi @KHorseman,

Thanks for the sharing! 

Regards