Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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,
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.
Hi @luxpbi
Could you share your powerbi file.
Probably, the calculated column needs optimization.
Regards
Kumail
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º S | Date | Status | Type | Type Name | Product | Index | Days |
A | 12/06/2017 | 7 | X | 10175089 | 11 | ||
A | 22/06/2017 | 2 | X | 10213994 | 2 | ||
A | 23/06/2017 | 7 | X | 10216732 | 15 | ||
B | 07/07/2017 | 2 | X | 10274288 | 2 | ||
B | 08/07/2017 | 2 | X | 10275632 | 1 | ||
B | 08/07/2017 | 7 | X | 10276544 | 15 | ||
B | 22/07/2017 | 2 | X | 10331471 | 1 | ||
B | 22/07/2017 | 8 | X | 10333186 | 3 | ||
B | 24/07/2017 | 2 | X | 10340907 | 1 | ||
B | 24/07/2017 | 7 | X | 10341725 | 19 | ||
B | 11/08/2017 | 2 | X | 10411062 | 1 | ||
C | 11/08/2017 | 12 | X | 10414030 | 1 | ||
C | 11/08/2017 | 2 | X | 10415081 | 2 | ||
C | 12/08/2017 | 7 | X | 10417554 | 15 | ||
C | 26/08/2017 | 2 | X | 10475511 | 1 | ||
C | 26/08/2017 | 2 | X | 10476606 | 2 | ||
C | 27/08/2017 | 7 | X | 10482842 | 8 |
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
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?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
88 | |
86 | |
82 | |
66 | |
49 |
User | Count |
---|---|
138 | |
110 | |
104 | |
66 | |
64 |