Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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 III
Advocate III

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
Post Prodigy
Post Prodigy

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.