Forum Discussion
Measure Optimization - IF() Reduction
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 Of course. I've uploaded it to GDocs.
Sample Data: https://drive.google.com/file/d/1Uu53QZFkrziBEdoLzORoY__dWnFJlnsj/view?usp=sharing
Sample PBI File: https://drive.google.com/file/d/1yxISUan6KIdk6nVKMUuKQTSGSLo80IYF/view?usp=sharing
The table is returning what I expect in the EstimateQualityCheck measure (complete/incomplete). However, the aggregation isn't correct in the CompleteCount measure.
I'd expect that the 8 "Incompletes" would be excluded from the CompleteCount measure.
The final calculation should return something in the ballpark of (147-8)/147 = 94.5%
In other words, why aren't these incomplete lines removing themselves from the calculation?
I also can't figure out why the CompleteCount is 148, not 147. The Distinctcount([SalesHistory]SO) is working in the TotalCount measure. But not when it's nested in the CompleteCount measure.
Thanks in advance for your continued support.
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
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.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 That's perfect.
When incorporating with the rest of my company data, I went from ~33 seconds per operation to 8.9. Fantastic!