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 

 

I am running into another direction.

 

If you are not calling a measure, but write them, wrap CALCULATE otherwise you sum up the whole table

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 )
    )
RETURN
    COUNTROWS ( FILTER ( T1, [EstimateQuality1] = "Complete" ) )

 

 If you do have those measures, you can simply call it

test1 =
VAR T1 =
    ADDCOLUMNS ( VALUES ( SalesHistory[SO] ), "test111", [EstimateQualityCheck] )
RETURN
    COUNTROWS ( FILTER ( T1, [test111] = "Complete" ) )

I am not sure how your measure was working - COUNTAX part, but it basically counts the total row of your dummy data table -  there is one SO with 2 entries

Anonymous's avatar
Anonymous
Not applicable
5 years ago

That sure does work!  Thanks so much.

 

One last thing.  How would you incorporate a final output of:

Divide([Test],[TotalCount]... But do it in the same measure.

  • 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!