Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Measure Optimization - IF() Reduction

Hello - I am trying to see if I can reduce the number of steps in the following measure.  Sub Measures are as follows:   [EstimatedTotalCost] is the sum of 4 individual columns that sit in the same...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

     

    So you go with one measure, the same test

    test = 
VAR T1 =
    GENERATE (
        GROUPBY ( SalesHistory, SalesHistory[SO] ),
        VAR EstimatedLaborCost =
            CALCULATE ( SUM ( SalesHistory[Estimated LaborCost] ) )
        VAR EstimatedSubCost =
            CALCULATE ( SUM ( SalesHistory[Estimated SubcontractorCost] ) )
        VAR EstimatedMaterialCost =
            CALCULATE ( SUM ( SalesHistory[Estimated MaterialCost] ) )
        VAR EstimatedOtherCost =
            CALCULATE ( SUM ( SalesHistory[Estimated OtherCost] ) )
        VAR EstimatedTotalCost = EstimatedLaborCost + EstimatedSubCost + EstimatedMaterialCost + EstimatedOtherCost
        VAR EstimateQuality1 =
            SWITCH (
                TRUE (),
                ISBLANK ( EstimatedTotalCost ), BLANK (),
                ( EstimatedTotalCost > 0
                    && EstimatedMaterialCost = EstimatedTotalCost )
                    || EstimatedTotalCost = 0, "Incomplete",
                "Complete"
            )
        RETURN
            ROW ( "EstimateQuality1", EstimateQuality1 )
    )

VAR CompleteCount =    COUNTROWS ( FILTER ( T1, [EstimateQuality1] = "Complete" ) )
VAR TotalSO = COUNTROWS(VALUES(SalesHistory[SO]))
RETURN
DIVIDE(CompleteCount,TotalSO)

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous 

 

It does not matter whether the code is long or short...but the performance and the side effect... so have you tried using SWITCH instead of nested IFs? Simply change it, not sure if it is working for your logic...have a test

 

EstimateQuality =
VAR estimatedtotalcost = [EstimatedTotalCost]
VAR estimatedlaborcost = [SUMLaborCost]
VAR estimatedsubcost = [SUMSubCosts]
VAR estimatedmaterialcost = [SUMMaterialCost]
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( estimatedtotalcost ), BLANK (),
        estimatedsubcost > 0, "Complete Estimate",
        estimatedmaterialcost > 0
            && estimatedlaborcost = 0, "Material, No Labor",
        estimatedtotalcost = 0, "No Material, No Labor",
        "Complete Estimate"
    )

 

Johanno good point to always use DAX formatter😁