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 try to remove "+0" and compare your performance, eventually update your "solution"

Justas4478's avatar
Justas4478
Post Prodigy
2 years ago

some_bih that reduced load time massively.
However it brings back probem that I had before.
Since I have this measure:

Days Shorted group = SWITCH('Outbound Delivery'[Days Shorted],0,"0 days Short",1,"1 days Short",2,"2 days Short",3,"3 days Short",4,"4 days Short","5 days Short or more")
If I remove  '+0' then values returned for 0 are blank and 'Days Shorted group' measure fails to capture them.
These are results that I get.

Is there any way to implement '+0' without creating massive time increase or to make 0 show up as 0 and not blank?

  • 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.