Performance issue while a report start

[Anonymous]

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 !!

[v-shex-msft · Accepted solution]

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

[Daryl-Lynch-Bzy]

@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 ) )

[Anonymous]

@Daryl-Lynch-Bzy  i can do it in private , i will send you a message

[Daryl-Lynch-Bzy]

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?

[Anonymous]

Hi @Daryl-Lynch-Bzy 

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