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

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