Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

IF Statement Compare Row Value to Measure

I have a measure calculated to find the value of a data set two standard deviations above the median:

Median From 2StdDev = CALCULATE(MEDIAN(Edge_Cycles[cycleTime]) + STDEV.P(Edge_Cycles[cycleTime]) * 2, ALLEXCEPT(Edge_Cycles, Edge_Cycles[shiftName], Edge_Cycles[cycleTime]))
 
There are also two page level filters applied: Edge_Cycles[cycleTime] < 90 and the ability to select unique shift names from Edge_Cycles[shiftName] (ie. 07-Nov-2019 -- Day Shift)
 
I am using ALLEXCEPT to make sure that the measure considers all values in a data table but also reacts to the applied filters. The Median From 2StdDev calculation works as expected - calculating the median + 2 standard deviations of all cycleTimes < 90 minutes long and within the shift(s) selected:

I then attempt to compare the cycleTime to the Median From 2StdDev for each row to find which cycle times are above the Median From 2StdDev values using and IF statement. However, it seems that because the comparison is being done on a row by row basis, the Median From 2StdDev in the IF statement becomes calculated for each row rather then the whole dataset filtered by cycleTime < 90 min and selected shiftNames. Therefore I can't make the comparison. 

 

The closest I can get is the following IF statement: 

Cycle Time 2StdDev = IF(Edge_Cycles[cycleTime] > CALCULATE(Edge_Cycles[Median From 2StdDev], ALLEXCEPT(Edge_Cycles, Edge_Cycles[shiftName]), FILTER(Edge_Cycles, Edge_Cycles[cycleTime] < 90)), 1, 0)
 

I have both an ALLEXCEPT and a FILTER applied in the CALCULATE statement trying to force the Median From 2StdDev Measure to use the whole dataset. The ALLEXCEPT and FITLER each work separately, but when applied together, the FILTER seems to overwrite the effects of the ALLEXCEPT.

 

What would be the best way to compare a calculated measure to individual rows in a table without having the measure recalculated for each row?

 

Need Help

2 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    6 years ago

    Anonymous ,

     

    You may modify the measure using dax below:

    Cycle Time 2StdDev =
IF (
    Edge_Cycles[cycleTime]
        > CALCULATE (
            Edge_Cycles[Median From 2StdDev],
            FILTER (
                ALLEXCEPT ( Edge_Cycles, Edge_Cycles[shiftName] ),
                Edge_Cycles[cycleTime] < 90
            )
        ),
    1,
    0
)

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      v-yuta-msft 

       

      Your modification works for a comparision with each separate shift, but does recalculate considering data from all selected shifts. Your solution is acceptable for the report I am building, but does not answer my specific question above. 

       

      I modified your function to display the Median From 2StdDev instead of 1 when True. This shows that the Calculated Median From 2StdDev is being split by shift:

      Ideally, the cycleTime for each row would be compared against the Median + 2 Std Deviations calculated for the entire filtered dataset. It would adjust as new shifts are selected and the same value would apply for all rows:

       

      Instead, the Median + 2 Std Deviation calculation is split by shift as explained above.