creating measures take long time

[pade]

Having large datasets (~1 GB), creating new measures take vary long time, sometimes up to some minutes. It takes long time just from pusing the Create New Measure button untill the editor window is available as well as when the Measure is saved. Anyone else having the same issues and that knows the reason behind

[Arman11]

Dear All,
I still have the same problem over 2 years later. Is there a proper solution nowadays? 
Thanks!

[pade]

Just replying with some lernings. 
Building larger datasets, use tabular editor
Even for smaller datasets, this is a problem if you have many calculated tables and columns. In that case, could you instead have used PQ for creating these calculated columns and tables? Yes... replacing a calculated table (based on another table) with a PQ table will most probably generate a slower ETL since PQ has quite a small cach and therefore will draw the same information a second time from the data source. Using PBI dataflows can help here in that case.

But... Doing all this work in a wherehouse or datalake is most time the best choise and then only do measures in PBI

[RobbeVL]

The reason is your dataset is way too large.

If you are working with a database, write some smaller queries to aggregate your data.

[pade]

Thanks @RobbeVL , but please note that it's not the measure execution time that is long, it the creation of the measures that takes a very long time. The same thing whenever I change a measure.

Imaging creating a measuer like below:

foo = true()

Creating this very simple measure takes 3-4 minutes all together. Yes I know my dataset is large, but there are reasons for that and we are looking into both aggregations and other teqniques to decrease the size

[RobbeVL · Accepted solution]

Yes, again!

Whenever you create a measure, PowerBI reviews its dataset to see what could inflict the new measure.
Having an imported dataset of +1gb is not fun to work with.

Is working with DirectQuery a possibility? This wil only load the data you actually use.

[pade]

Thanks @RobbeVL ,

Yes I can understand validation is needed, but I guess review is not all that happens. If you e.g. crates a calculated table that in one of the columns generates random numbers, you will see that each time you create a new measure (could be as simple as Measure=1), the values in the random column are re-calculated. I can't see why this is happening, and if this is part of the reason that it takes a long time to create new measures I guss it's something that Microsoft can do better

[gmelhaff]

I agree and would add this is not really acceptable.  I would question the idea that anything larger than 1 gb is large.   I've already cut down my facts to only a few years with the pbix down to 800k (was 1.5 gb) and still adding a measure takes minutes each time.  We do this same thing in SSAS and it's instantaneous.  Why is PowerBI desktop so much slower than visual studio with SSAS?  btw.....I'm running the same terminal server to do both SSAS and PBI desktop.

[pade]

Nowadays Tabular Editor is the way to go here, but still...

[gmelhaff]

I haven't used tabular editor other to unsuccessful attempt to use it to convert from SSAS to datasets.  Will give that a try for development work. 

In fear of preaching to the choir...So curious why Microsoft doesn't provide the tabular editor themselves?  So much emphasis on this tool and yet so little support for enterprise types of development.  What would make WAY to much sense is if they simply supported datasets in SSDT like SSAS. Then transitioning from SSAS to PowerBI datasets would be basically seemless rather than having to start over again in PowerBI.  Come on Microsoft....its the same product behind the scenes...seems insanity they are making this so difficult.