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 

 

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

Anonymous That's perfect.

 

When incorporating with the rest of my company data, I went from ~33 seconds per operation to 8.9.  Fantastic!