DAX optimization

[Anonymous]

Hi Experts

I am trying to optimize the following DAX measure, not sure if there is a better way to re writre the current measure as the visual assoicated with the measure is close to timing out i.e. 225 seconds (the not enough resources available error)

Cumulative Complaint Rate_ =

VAR Cumulative_Complaints =

CALCULATE (

[TotalComplaintswithTrends],

FILTER (

ALLSELECTED ( PMS_FINANCIAL_PDS ),

PMS_FINANCIAL_PDS[Month Start] <= MAX ( PMS_FINANCIAL_PDS[Month Start] )

)

)

VAR Cumulative_Sales =

CALCULATE (

[TotalSalesTrend],

FILTER (

ALLSELECTED ( PMS_TM1_SALES_VOLUME ),

[Month Start] <= MAX ( PMS_FINANCIAL_PDS[Month Start] )

)

)

RETURN

IF (

DIVIDE ( Cumulative_Complaints, Cumulative_Sales, 0 ) <> 0,

DIVIDE ( Cumulative_Complaints, Cumulative_Sales, 0 ))

[v-alq-msft]

Hi, @Anonymous 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Here are some suggestions about optimize your model.

• 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).
• Use integers instead of strings, where possible.
• Be wary of DAX functions, which need to test every row in a table – for example, RANKX – in the worst case, these functions can exponentially increase run-time and memory requirements given linear increases in table size.
• When connecting to data sources via DirectQuery, consider indexing columns that are commonly filtered or sliced again. Indexing greatly improves report responsiveness.  

For further informatiom, please refer to the official document .

Best Regards

Allan

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

[Pragati11]

Hi @Anonymous ,

One thing I can note on your DAX expression is the RETURN part. Do you really need that IF condition on DIVIDE function?

Without IF condition, it should do same. Try replacing RETURN part in the expression with:

RETURN

DIVIDE ( Cumulative_Complaints, Cumulative_Sales, 0 )

Let me know if this change gives you the similar output as before or not; also if it improves any performance.

If this helps please give Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[Anonymous]

Thanks Pragati11. just trying out your suggestion.

[amitchandak]

If this month start is a date. Create a date table move the calcultaion there.

Example

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all(date),date[date] <=maxx(date,date[date])))

See if it improves performance

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Anonymous]

hi Amit

here is my date table. i have shown where i want the cumulative formula to start and end at for each of the 3 periods. I have included the excel formula on how the result is to be calculated. you could use the rolling month column??? this splits the periods up too