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 ,


Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your problem.


Best Regards,
Stephen Tao

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Since posting, I've gone another direction, using a combination of SWITCH() and combining all measures into multiple VARs.

 

To summarize, the measure is calculating total costs (EstimatedTotalCost), determining if an estimate is "Complete" or Incomplete" (Estimate Quality), Counting all estimates (CountAllEstimates), counting all complete estimates (CompleteEstimateCount), and calculating the ratio of complete to total estimates (EstimateQuality%).

 

In silos, each of the VARs are working as expected, except for CompleteEstimateCount. It counts all rows, not just the rows that EstaimteQuality is calling "Complete".

 

When I drop the EstimateQuality measure into a data table, it does call out the Sales Orders (SOs) that are and are not "Complete".

 

This leads me to believe that there is an issue with my COUNTAX() measure.  I have to use Distinctcount() for 'Sales History'[SO] -> There are cases where the SO has multiple service lines.  In those cases, there are two of the same SOs, but I don't want to count it as two.  Using Distinctcount() fixes that problem.

 

In summary, all of the VARs work in their own silos, when I try to count the "Complete" SOs, the measure counts all of the SOs, not just the ones that EstimateQuality has determined as complete.

 

Greg_Deckler 

Kudo's to the following post for getting me down this thought process. 
https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275

 

 

 

Estimatequality% = 

VAR EstimatedLaborCost =
    SUM ( SalesHistory[Estimated LaborCost] )

VAR EstimatedSubCost =
    SUM ( SalesHistory[Estimated SubcontractorCost] )

VAR EstimatedMaterialCost =
    SUM ( SalesHistory[Estimated MaterialCost] )

Var EstimatedOtherCost = 
    SUM(Sales[Estimated OtherCost])

Var EstimatedTotalCost =
SUMX (
        SalesHistory,
        EstimatedLaborCost + EstimatedSubCost + EstimatedMaterialCost + EstimatedOtherCost
    )

Var EstimateQuality =
SWITCH(
    TRUE(),
        ISBLANK(EstimatedTotalCost),BLANK(),
        EstimatedTotalCost > 0
            && EstimatedMaterialCost = EstimatedTotalCost
            || EstimatedTotalCost = 0 , "Incomplete",
            "Complete"
)

Var CountAllEstimates = 
DISTINCTCOUNT(SalesHistory[SO])

Var CompleteEstimateCount = 
    COUNTAX(FILTER(SalesHistory,estimatequality="Complete"),DISTINCTCOUNT(SalesHistory[SO]))
    
Return

DIVIDE(CompleteEstimateCount,CountAllEstimates)

 

Anonymous