cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
luxpbi
Helper V
Helper V

Power BI Desktop Extremely Slow

Hi,

The fact is that I have a not very big pbix. It's 32MB size.
I've create a calculated column, a little bit complex, but it works.

The problem is that since then the pbix is extremely slow, for example if i make any change on a mesaure or click to create a measure it will take around 15-20 minutes with the message "working on it". 
Why Power BI does that? It as no sense at all for me...
Even if the problem is the new calculated column, click on create a new measure shouldn't affect at all my report.

 

Am I missing something? 

Thank you for your help.

 

 

Regards,

13 REPLIES 13
markus_zhang
Advocate II
Advocate II

I found that Power BI will refresh for very small changes (like copy a value from a table, not even pasting it), and each refresh may take a long time. But 15-20 minutes is definitely too much.

Kumail
Super User
Super User

Hi @luxpbi

 

Could you share your powerbi file.

 

Probably, the calculated column needs optimization.

 

Regards

Kumail

 

v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @luxpbi,

 

Please refer to your similar thread.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-piga-msft,

 

They are not similar, here I'm asking why Power BI get a lot of time working on it when I select to create a new measure. 
What is he doing there? Why a complex or not optimized DAX Measure or Column will afect that? 
For me there is no reason for that. 
In the other, I'm asking for a tool that can measure the performance of different DAX measures.

 

@Kumail I can't share the pbix file for security reasons but I can share with you the formula: 

Days =
VAR CurrentProduct = 'Table1'[Product]
VAR CurrentState = 'Table1'[Status]
VAR CurrentDate = 'Table1'[Date]
VAR CurrentNMov = 'Table1'[Index_]
VAR CurrentCodTaller = 'Table1'[Type]
VAR NextStateDate =
    CALCULATE (
        MIN ( 'Table1'[Date] );
        FILTER (
            'Table1';
            'Table1'[Product] = CurrentProduct
                && ( 'Table1'[Status] <> 8
                || 'Table1'[Status] <> 9 )
                && 'Table1'[Date] >= CurrentDate
                && 'Table1'[Index_] > CurrentNMov
                && (
                    'Table1'[Type] <> CurrentCodTaller
                        || 'Table1'[Type] = BLANK ()
                        || 'Table1'[Type] = CurrentCodTaller
                )
        )
    )
VAR LastMov =
    CALCULATE (
        MIN ( 'Table1'[Date] );
        FILTER (
            'Table1';
            'Table1'[Product] = CurrentProduct
                && 'Table1'[Date] >= CurrentDate
                && 'Table1'[Index_] > CurrentNMov
        )
    )
RETURN
    IF (
        CurrentState = 8
            || CurrentState = 9;
        IF (
            ISBLANK ( LastMov );
            ( TODAY () - CurrentDate )
                + 1;
            IF ( ISBLANK ( NextStateDate ); 1; ( NextStateDate - CurrentDate ) + 1 )
        )
    )

 

Regards,

Okay. Could you send sample dummy data as well

Hi @Kumail

 

Sorry for the delay, but if being very busy. 
This is some dummy Data: 

Nº SDateStatusTypeType NameProductIndexDays
A12/06/20177  X1017508911
A22/06/20172  X102139942
A23/06/20177  X1021673215
B07/07/20172  X102742882
B08/07/20172  X102756321
B08/07/20177  X1027654415
B22/07/20172  X103314711
B22/07/20178  X103331863
B24/07/20172  X103409071
B24/07/20177  X1034172519
B11/08/20172  X104110621
C11/08/201712  X104140301
C11/08/20172  X104150812
C12/08/20177  X1041755415
C26/08/20172  X104755111
C26/08/20172  X104766062
C27/08/20177  X104828428

 

Please, notice that I've changed a little bit my code, now it looks like this: 

Days = 
VAR CurrentProduct = 'Table1'[Product]
VAR CurrentState = 'Table1'[Status]
VAR CurrentDate = 'Table1'[Date]
VAR CurrentNMov = 'Table1'[Index_]
VAR CurrentCodTaller = 'Table1'[Type]
VAR NextStateDate =
    CALCULATE (
        MIN ( 'Table1'[Date] );
        KEEPFILTERS ( 'Table1'[Status] <> 8
            || 'Table1'[Status] <> 9 );
        FILTER (
            'Table1';
            'Table1'[Product] = CurrentProduct
                && 'Table1'[Date] >= CurrentDate
                && 'Table1'[Index_] > CurrentNMov
                && ( 'Table1'[Type] = BLANK () || 'Table1'[Type] <> CurrentCodTaller || 'Table1'[Type] = CurrentCodTaller )
        )
    )
VAR LastMov =
    CALCULATE (
        MIN ( 'Table1'[Date] );
        FILTER (
            'Table1';
            'Table1'[Product] = CurrentProduct
                && 'Table1'[Date] >= CurrentDate
                && 'Table1'[Index_] > CurrentNMov
        )
    )
RETURN
    IF (
        ISBLANK ( LastMov );
        ( TODAY () - CurrentDate )
            + 1;
        ( NextStateDate - CurrentDate )
            + 1
    )

Hi @luxpbi

 

I have tested the dax expression. Initially, it was giving number of syntax errors, however, it worked with some minor tweaks. The image and file is attached for your reference.

 

Regards

Kumail

Analytics and BI Practitioner

Email: kumail.raza@datavisualz.com Skype: kumailrazakazi

Screenshot 2018-08-29 11.52.50.png

Just figured that file upload option is not available here. 

May be you can contact on my email id for file or any other option to share you the file.

 

Regards

TestDays = 
VAR CurrentProduct = 'Table1'[Product]
VAR CurrentState = 'Table1'[Status]
VAR CurrentDate = 'Table1'[Date]
VAR CurrentNMov = 'Table1'[Index]
VAR CurrentCodTaller = 'Table1'[Type]
VAR NextStateDate =
    CALCULATE (
        MIN ( 'Table1'[Date] ),
        KEEPFILTERS ( 'Table1'[Status] <> 8
            || 'Table1'[Status] <> 9 ),
        FILTER (
            'Table1',
            'Table1'[Product] = CurrentProduct
                && 'Table1'[Date] >= CurrentDate
                && 'Table1'[Index] > CurrentNMov
                && ( 'Table1'[Type] = BLANK () || 'Table1'[Type] <> CurrentCodTaller || 'Table1'[Type] = CurrentCodTaller )
        )
    )
VAR LastMov =
    CALCULATE (
        MIN ( 'Table1'[Date] ),
        FILTER (
            'Table1',
            'Table1'[Product] = CurrentProduct
                && 'Table1'[Date] >= CurrentDate
                && 'Table1'[Index] > CurrentNMov
        )
    )
RETURN
    IF (
        ISBLANK ( LastMov ),
        ( TODAY () - CurrentDate )
            + 1,
        ( NextStateDate - CurrentDate )
            + 1
)

Even in measure, its working perfectly fine; as fast as any other feature in power bi

Works fast because of the size of the dataset. When you have 2 million rows is veeeery slow.. 
Do you think is there a way to optimize it?

Not sure. Actually I was extremely busy on something therefore, couldn't reply you yesterday.

 

I have used python for my clients for processing large dataset and created a csv or excel file for visualization (If you are not querying data directly from CRM). That generally works well for me.

 

 

Hi @Kumail,

 

Thank you for your time and your answer, but I can't really se the code there, maybe you could post it in a comment.
In the meantime I have made some modifications and I made it simplier and it works fine.

Days =
VAR CurrentProduct = 'Table1'[Product]
VAR CurrentDate = 'Table1'[Date]
VAR CurrentNMov = 'Table1'[Index_]
VAR NextStateDate =
CALCULATE (
MIN ( 'Table1'[Date] );
FILTER (
'Table1';
'Table1'[Product] = CurrentProduct
&& 'Table1'[Date] >= CurrentDate
&& 'Table1'[Index_] > CurrentNMov
)
)
RETURN
IF (
ISBLANK ( NextStateDate );
( TODAY () - CurrentDate )
+ 1;
( NextStateDate - CurrentDate )
+ 1
)


What I'm thinking is that maybe instead of a calculated column I could use a measure.
Have you tested my original code and yours with DAX Studio?

Regards,

Okay. Could you send sample dummy data as well?

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors