DAX query optimisation

[Justas4478]

Hi, I have this DAX query:

Days Shorted = VAR _Table =

    SUMMARIZE(

         'Outbound Delivery',

         'Product Category'[Level 2],

         'Date'[Date],

         "@Shorted", 'Outbound Delivery'[Shorted Qty]

    )

VAR _Result =

    COUNTX(

        FILTER(

             _Table,

              [@Shorted] <> 0

        ),

        [@Shorted]

    )+0

RETURN

  _Result

The query takes long time to run, I was hoping there is any ways that it can be optimised to make it faster.
Thanks.

[some_bih]

Hi @Justas4478 try to remove "+0" and compare your performance, eventually update your "solution"

[Justas4478]

@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]

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]

@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]

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

[Justas4478]

@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]

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

[Anonymous]

Hi  @Justas4478 ,

As far as I know, the summarize() function creates a table, and creating a table is an operation that generally consumes a large amount of time, so you might consider optimizing performance by removing extended columns from summarize() and adding them using the addcolumns() function.

Optimize SUMMARIZE with ADDCOLUMNS in Dax #ssas #tabular #dax #powerpivot - SQLBI

Best practices using SUMMARIZE and ADDCOLUMNS - SQLBI

There are also some suggestions for optimizing the model here:

• Remove unused tables or columns, where possible. 
• Avoid distinct counts on fields with high cardinality – that is, millions of distinct values.  
• Take steps to avoid fields with unnecessary precision and high cardinality. For example, you could split highly unique datetime values into separate columns – for example, month, year, date, and so on. Or, where possible, use rounding on high-precision fields to lower cardinality – (for example, 13.29889 -> 13.3).
• When connecting to data sources via Direct Query, consider indexing columns that are commonly filtered or sliced again. Indexing greatly improves report responsiveness. 

You can use DAX Studio to optimize the inspection of DAX statements from a performance perspective and use the Performance analyzer to check the performance of each of your report elements when users interact with them, and which aspects of performance consume the most (or least) resources

https://daxstudio.org/

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-performance-analyzer

This is the related document, you can view this content:

Optimizing DAX expressions involving multiple measures - SQLBI

Improve Power BI Performance by Optimizing your DAX | by MAQ Software | MAQ Software | Medium

All the secrets of SUMMARIZE - SQLBI

Best Regards,

Liu Yang

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

[Justas4478]

@Anonymous Thanks I will try your suggestions.