How to simplify Long list measure

[sadiahsapiee]

Hi, 

I have a list of measure to get Risk Value and have no idea to simplify this measure. Not sure whether this list of measure contribute the report performance which very slow if user filter Market level only not customer level (lowest level).

Hope gurus can help me. Thanks in adcance
_Cust Risk Value = 
CALCULATE([_Cust Total 2], USERELATIONSHIP('Component-Customer Table'[CTDP Comp Category], 'Component Category'[Comp Category]))

** this because if customer not selected the value not appear or not calculate correctly
_Cust Total 2 = IF(HASONEVALUE(CUSTMASTEREXPORT[CUSTOMER_ID]),[_Cust E&O Total],SUMX(VALUES(CUSTMASTEREXPORT[CUSTOMER_ID]),[_Cust E&O Total]))

**** this because if sale item id not selected the value not appear or not calculate correctly

_Cust E&O Total = If(HASONEVALUE(PRODMASTEREXPORT[SALES_ITEM_ID]), [_Cust E&O Risk value], SUMX(VALUES(PRODMASTEREXPORT[SALES_ITEM_ID]),[_Cust E&O Risk value]))

_Cust E&O Risk value = [Customer Demand Total2] * [#Cust_ E&O Cost]

Customer Demand Total2 = IF(HASONEVALUE(CUSTMASTEREXPORT[CUSTOMER_ID]),[Customer Demand Total], SUMX(VALUES(CUSTMASTEREXPORT[CUSTOMER_ID]),[Customer Demand Total]))

Customer Demand Total = IF(HASONEVALUE(PRODMASTEREXPORT[SALES_ITEM_ID]),[Customer Demand], SUMX(VALUES(PRODMASTEREXPORT[SALES_ITEM_ID]),[Customer Demand]))

Customer Demand =AVERAGE('Component-Customer Table'[CTDP])

#Cust_ E&O Cost = CALCULATE(ROUND([#Cust_BOM Cost],2),'Component-Customer Table'[CTDP Comp Category] = "Immediate Risk")

#Cust_BOM Cost =

var cust = SELECTEDVALUE(CUSTMASTEREXPORT[CUSTOMER_ID])

var Si = SELECTEDVALUE(PRODMASTEREXPORT[SALES_ITEM_ID])

var DMTKSI = cust & Si

return

CALCULATE(SUM('Component-Customer Table'[COST]),'Component-Customer Table'[Cust-SI] = DMTKSI)

[v-heq-msft]

Hi @sadiahsapiee ,
Power BI performance can be affected by complex calculations, especially when they are not optimized for the filter context in which they are being used. To go about optimizing the performance of measure,  you can try to use aggregations at the highest level possible because functions like SUMX and FILTER can be expensive on large datasets.
To de-simplify your measure, you can combine similarity measures and use variables to store common expressions.

Here's an example of how you might simplify your measures using variables:

Cust Risk Value Simplified = 
VAR CustomerSelected = HASONEVALUE(CUSTMASTEREXPORT[CUSTOMER_ID])
VAR SalesItemSelected = HASONEVALUE(PRODMASTEREXPORT[SALES_ITEM_ID])
VAR CustomerDemandTotal = 
    IF(
        CustomerSelected,
        [Customer Demand Total],
        SUMX(VALUES(CUSTMASTEREXPORT[CUSTOMER_ID]), [Customer Demand Total])
    )
VAR CustEoTotal = 
    IF(
        SalesItemSelected,
        [_Cust E&O Risk value],
        SUMX(VALUES(PRODMASTEREXPORT[SALES_ITEM_ID]), [_Cust E&O Risk value])
    )
VAR CustEoRiskValue = CustomerDemandTotal * [#Cust_ E&O Cost]
RETURN
    CALCULATE(
        CustEoTotal,
        USERELATIONSHIP('Component-Customer Table'[CTDP Comp Category], 'Component Category'[Comp Category])
    )

This is just an example and may not work perfectly with your data model. You'll need to adjust the logic to fit your specific scenario.

Best regards

Albert He

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