Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
109 | |
99 | |
39 | |
31 |