topic Re: Performance issue while a report start in DAX Commands and Tips

Performance issue while a report start

Anonymous — Tue, 14 Jun 2022 22:25:43 GMT

Hi,

I have a report with live connection (ssas tabular) i'm facing an issue when i start the report as some calculaction up to 50 seconds to show up.

I did the performance analyzer on power bi and it seems like the issue is caused by the bellow dax calculation :

CALCULATE ( [Cnt], SUMMARIZE ( Sales, DimProduct[Product_id] ) ,SUMMARIZE ( Forecast, DimProduct[Product_id] ) )

The definition of the measure Cnt like as bellow :

Cnt := DISTINCTCOUNT ( Invoice[CustoID] )

Could you please advise me on what can be the issue with the calculation and if there is any alternative for the Summarize function ?

Thanks for help !!

Re: Performance issue while a report start

Anonymous — Wed, 15 Jun 2022 17:08:43 GMT

Hi, Anonymous - I am trying to understand the business requirement for the measure. It appears that you want the Number of Unique Customers which who brought products that appear in the Sales and Forecast table? Your approach to calculating this will not be performant because the distinctcount function will require a complete table scan and this after performing a complete table scan on Sales and Forecast. If you use Dax Studio to analyse the DAX Query obtained from performance analyser, you will see lots of SE Queries and FE processing. To help further, I would need to see the Data Model and some sample data. Is it possible to provide a link to a PBIX?

Re: Performance issue while a report start

Anonymous — Wed, 15 Jun 2022 18:56:14 GMT

Hi Anonymous

Thanks for the reply.

yes i did the troubleshooting with Dax studio and i confirm that i have a lot of of SE Queries and FE processing.

This the reason why i share the question , to have some recommendation to improve that .

Yes you are right for the business requirement.

Thanks

Re: Performance issue while a report start

Anonymous — Wed, 15 Jun 2022 19:20:11 GMT

Anonymous - It would help to see the data model. I am thinking the Invoice table has Bidirectional Relationships with Sales and Forecast - another performance no no. Here is what I am thinking. If you create VAR to find the Sales and Forecast products, then Join tables to find the Distinct Customer ID. This would look something like this:

Distinct Customers Count for Sales & Forecast Products = 
VAR _sales = SUMMARIZECOLUMNS( Product[Product_id] , Invoice[CustoID] , "Sum" , Sales[Amount] )
VAR _forecast = SUMMARIZECOLUMNS( Product[Product_id], "Sum" , Sales[Amount] )
VAR _Join = NATURALINNERJOIN ( _sales , _forecast )
VAR _Customers = SELECTCOLUMN( _Join , "Customers" , Invoice[CustoID] )
RETURN
COUNTROWS ( DISTINCT ( _Customers ) )

Re: Performance issue while a report start

Anonymous — Wed, 15 Jun 2022 19:44:10 GMT

Anonymous i can do it in private , i will send you a message

Re: Performance issue while a report start

Anonymous — Wed, 22 Jun 2022 01:53:27 GMT

Hi Anonymous,

Perhaps you can try to use the following measure formula to calculate results without summary across multiple tables:

formula = VAR currProduct = VALUES ( DimProduct[Product_id] ) VAR saleList = CALCULATETABLE ( VALUES ( Sales[InvoiceID] ), FILTER ( ALLSELECTED ( Sales ), Sales[Product_id] IN currProduct ) ) VAR forcastList = CALCULATETABLE ( VALUES ( Forecast[InvoiceID] ), FILTER ( ALLSELECTED ( Forecast ), Forecast[Product_id] IN currProduct ) ) RETURN CALCULATE ( COUNTROWS ( VALUES ( Invoice[CustoID] ) ), FILTER ( ALLSELECTED ( Invoice ), [InvoiceID] IN INTERSECT ( saleList, forcastList ) ) )

Regards,

Xiaoxin Sheng