Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Directquery is slow with measure

Hi there

 

I have a p&l report that uses the DirectQuery functionality. The source-table has around 20k rows with 10 columns. 

 

The P&L table I've created has the following measure: =

W&V RJ = CALCULATE(sum(FACT_rows[amount]),FILTER(FACT_rows,FACT_rows[Financial_year] = ReportingYear[ReportingYear Value]))
 
When I update a slicer it takes around 12 seconds to reload the table with the corresponding values.
 
My question is, why is it so slow? The performance analyzer says it's the measure. But the measure is not even complex?
 
Thank you
Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , is it a measure or column ReportingYear[ReportingYear Value] ?

     

    I doubt it will work directly

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Measure

      • v-chenwuz-msft's avatar
        v-chenwuz-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi Anonymous ,

         

        It's hard to see why it's slow with one formula alone. One possibility is that your server is running slowly.
        You can try to copy this pbix file and change the Direct query mode to import mode in copy file. test the performance of this measure again.

        How to change storge mode? In Model screen, select this table and change DirectQuery to import under the advanced setting, at the bottom right corner of model screen.

        Or

        Use extranl tool DAX studio to test it.

        Here is the guide for you to optimize your dax.

        https://maqsoftware.com/insights/dax-best-practices 

         

        Best Regards

        Community Support Team _ chenwu zhu

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Someone?