Forum Discussion

luxpbi's avatar
luxpbi
Helper 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
luxpbi's avatar
luxpbi
Helper 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
Impactful 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

  • Kumail's avatar
    Kumail
    Impactful Individual
    8 years ago

    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

    • luxpbi's avatar
      luxpbi
      Helper V
      8 years ago

      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,

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

      • luxpbi's avatar
        luxpbi
        Helper V
        8 years ago

        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?