Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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:

DataTable_1.png

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?

 

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

@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
Not applicable

@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:

ByShift.png

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:

AllSelected.png

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.