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
    )