Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
luxpbi
Helper V
Helper V

Power BI Desktop Extremely Slow

‎08-23-2018 06:10 AM

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,

Labels:
Message 1 of 14
30,289 Views
13 REPLIES 13
markus_zhang
Advocate III
Advocate III

‎01-25-2019 07:38 PM

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.

Message 14 of 14
11,448 Views
Kumail
Post Prodigy
Post Prodigy

‎08-23-2018 10:25 PM

Hi @luxpbi

 

Could you share your powerbi file.

 

Probably, the calculated column needs optimization.

 

Regards

Kumail

 

Message 13 of 14
30,163 Views
0
v-piga-msft
Resident Rockstar
Resident Rockstar

‎08-23-2018 10:12 PM

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.
Message 2 of 14
30,168 Views
0
luxpbi
Helper V
Helper V
In response to v-piga-msft

‎08-23-2018 11:49 PM

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,

Message 3 of 14
30,156 Views
0
Kumail
Post Prodigy
Post Prodigy
In response to luxpbi

‎08-24-2018 12:10 AM

Okay. Could you send sample dummy data as well

Message 5 of 14
30,152 Views
0
luxpbi
Helper V
Helper V
In response to Kumail

‎08-27-2018 05:42 AM

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
    )
Message 6 of 14
30,138 Views
0
Kumail
Post Prodigy
Post Prodigy
In response to luxpbi

‎08-29-2018 12:00 AM

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

Message 7 of 14
30,126 Views
0
Kumail
Post Prodigy
Post Prodigy
In response to Kumail

‎08-29-2018 12:06 AM

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

Message 8 of 14
30,125 Views
0
Kumail
Post Prodigy
Post Prodigy
In response to Kumail

‎08-29-2018 01:59 AM 
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

Message 10 of 14
11,490 Views
0
luxpbi
Helper V
Helper V
In response to Kumail

‎08-29-2018 02:19 AM

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?

Message 11 of 14
11,487 Views
0
Kumail
Post Prodigy
Post Prodigy
In response to luxpbi

‎08-29-2018 02:27 AM

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.

 

 

Message 12 of 14
11,486 Views
0
luxpbi
Helper V
Helper V
In response to Kumail

‎08-29-2018 12:48 AM

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,

Message 9 of 14
30,114 Views
0
Kumail
Post Prodigy
Post Prodigy
In response to luxpbi

‎08-24-2018 12:08 AM

Okay. Could you send sample dummy data as well?

Message 4 of 14
30,153 Views
0

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

View All