Forum Discussion

luxpbi's avatar
luxpbi
Icon for Helper V rankHelper V
8 years ago

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...
Need Help
v-piga-msft's avatar
v-piga-msft
Icon for Resident Rockstar rankResident Rockstar
8 years ago

Hi luxpbi,

 

Please refer to your similar thread.

 

Best Regards,

Cherry

luxpbi's avatar
luxpbi
Icon for Helper V rankHelper V
8 years ago

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,

  • Kumail's avatar
    Kumail
    Icon for Impactful Individual rankImpactful Individual
    8 years ago

    Okay. Could you send sample dummy data as well?

  • Kumail's avatar
    Kumail
    Icon for Impactful Individual rankImpactful Individual
    8 years ago

    Okay. Could you send sample dummy data as well

    • luxpbi's avatar
      luxpbi
      Icon for Helper V rankHelper V
      8 years ago

      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
    )
      • Kumail's avatar
        Kumail
        Icon for Impactful Individual rankImpactful Individual
        8 years ago

        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: [email protected] Skype: kumailrazakazi