Performance Issue - Really Slow While Developing, probable datasource issue

[Anonymous]

Hi

My Power BI desktop product is super, super slow while i'm devloping it. 

Whenever i'm adding or updating a column or measure, it is giving me the  "Working on it" message for literally about 5 minutes at a time. Pressing save, same thing. Clicking to validate a change to a calculated column, same thing.

I have about 10 calculated columns. 

I choose calculated columns because i'm calculating row level results on a fact table. I don't have the knowledge to know a better way to do this. 

This lag started when:

I had an issue with the datasource, my fact table wasn't joining to my date dimension table because the fact tables date time wasn't matching up with the dimensions date due to the time element. 

I created a column in each table, using the format function to cast the date time columns as just dates. 

The new columns originally stored as text and i thought this was the cause of the slowness. I changed them to dates (it took ages!) and it has made no benefit to the lag. It is still taking 5 + minutes to make any changes.

What can i do?? this sucks. 

[v-juanli-msft]

Hi @Anonymous

Is your dataset very large?

First, please read power bi performance tips to know what would slow your performance and how to avoid it.

Second, If measures could also meet your requirements, you could create measures instead.

Calculated Columns vs Measures in DAX

In your scenario,

my fact table wasn't joining to my date dimension table because the fact tables date time wasn't matching up with the dimensions date due to the time element.

From above, i can conclude:

the key column to join fact table and date table is the "date" coulmn.

in fact table "date" column is of datetime type, while "date" column in the date table is of date type.

To solve above, 

You could duplicate "date" column(of datetime type) in the fact table,

then click on "Modelling"->"Data type", select "date". Then this column would be date type.

after this, create relationships between fact table and date table.

Or you could just duplicate "date" column(of date type) in the date table and transform it to "datetime" type.

Best Regards

Maggie

[GilbertQ · Accepted solution]

Hi there

If you are using a date table with relationships, I would suggest the following to hopefully improve performance.

Complete all the column modifications in the Power Query Editor. This will ensure that the data is persisted once it is loaded into the data model once only. It is also easier to do the conversions in the Power Query Editor.

Next go into the Options and settings and then Options for your PBIX and make sure to DESLECT Auto Date/Time and Allow data preview to download on background

The first one can make your model really large for every date or date/time column.

The second one will stop data being refreshed in the background, which can also really slow down your model.