Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Performance issue while a report start

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 ...
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

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