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)

     

Johanno's avatar
Johanno
Continued Contributor
5 years ago

If it works I think you should be satisfied and move on to the next challange. ğŸ˜€ There's nothing wrong using nested IF, sometimes you can avoid them by using SWITCH but SWITCH doesn't make any difference other than understanding the code since it's internally translated into nested IFs.

 

Sure, if you could add a column in Power Query or in the data source you might write it differently. The only thing I note is the formatting of the code (DAX Formatter is a great tool!) so it's easier to see which IFs are subordinate:

 

 

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

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks, Johanno   Definetly using Dax Formatter to accomplish this nicely cascaded format 🙂