Measure Flagging Performance

[SabineOussi]

Hello Community,

I come back here after a long while of not posting asking for your help 🙂

I'm presented with a request to identify low performance reasons based on predefined thresholds.

A sample data file with measures is provided here

Low performance identification relies on counting the vast majority of each category, this was achieved by flagging each categroy and summing by product or label.

The measures outcome is absolutely correct however the way they're written is slowing the whole report causing resources exceeded error on the service side.

ProductCategory categorizes the products based on Sales and Inquiries - no major issue in here
LowSalesReasons identifies the performance reason based on the majority of product categories, it provides the results by product - syntax issue flagging each product by tier and summing those to find the vast majority, one of the measures causing delays in rendering results

AggregateLowSalesReasons identifies the performance reason based on the majority of products LowSalesReasons, it provides the results by label - reasoning follows LowSalesReasons, syntax issue flagging each label by product performance reasons and summing those to find the vast majority, one of the measures causing delays in rendering results

EDIT: the results in the red bordered table are the ones we are after, this is why there was an aggregate measure created.

Taking into consideration that the actual file contains more than 5 million records, these measures are causing slowness report-wide.

Your help is higly appreciated in rewriting those. The reasoning is quite straightforward and it shows in the return statement but the conditioning and summing on those flags are weakly written.

Thank you,

Sabine O.

[Anonymous]

@SabineOussi 

My suggestion is use Switch to replace the nested if in the return expression in AggregateLowSales and Reason LowSalesReasons. 

DAX quick performance tip: Replace SWITCH with nested IF's. - SQL Service

For example in AggregateLowSales:

Return

Switch(True(),

_avgsales > 0.45, " ",

Low > (High + LowHigh), "Low Sales Rates",

High > (Low + LowHigh), "High Inquiries",

"Low Sales Rates & High Inquiries")

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[SabineOussi]

Thank you @Anonymous for the suggestion.

A reworked version of the measures can be found here 

LowSalesReasons_1 and AggregateLowSales_1 using COUNTROWS FILTER and SWITCH instead of IF.

It is still slow, even slower on the bigger dataset.

Keep in mind that my original dataset is 5M+ and grows bigger every day.

LowSalesReasons_2 was created referring a variable to a measure.

It is a bit faster (not yet acceptable fast) but returns incorrect results.

Appreciate your help.

Sabine O.