Forum Discussion

Justas4478's avatar
Justas4478
Post Prodigy
2 years ago

DAX query optimisation

Hi, I have this DAX query: Days Shorted = VAR _Table =     SUMMARIZE(          'Outbound Delivery',          'Product Category'[Level 2],          'Date'[Date],          "@Shorted", 'O...
Need Help
some_bih's avatar
some_bih
Community Champion
2 years ago

Hi Justas4478 

without model and relationships, it is hard to spot issue. Still try to replace VAR _Table with below code

 

VAR _Table =
ADDCOLUMNS(
SUMMARIZE(
'Outbound Delivery',
'Product Category'[Level 2],
'Date'[Date])
,
"@Shorted", 'Outbound Delivery'[Shorted Qty])

Justas4478's avatar
Justas4478
Post Prodigy
2 years ago

some_bih It definitelly made query much faster, but it still fails to load larger amount amount of data faster or because of this:

 

 

  • some_bih's avatar
    some_bih
    Community Champion
    2 years ago

    Hi Justas4478 without model and relationships, it is hard to spot issue.  

    • Justas4478's avatar
      Justas4478
      Post Prodigy
      2 years ago

      some_bih The one thing I noticed if I remove 
      This from the table rows:

      It loads data almoust instantly.
      This is realationship between 'Product' table and 'Outbound Delivery' table that holds query.
      Because it is using live data model I can't do any changes to set realationships.

      I dont know is this any helpfull.

      • some_bih's avatar
        some_bih
        Community Champion
        2 years ago

        Hi Justas4478 not enought to provide some solution from my side 🙂

        Play a bit with CALCULATETABLE so you filterout blank result (combination measure without +0 and filter <> blank rows for text column