Measure doesnt work as expected inside VAR

[ipezakas]

Hello community! 

I have the following issue: 
I have created a visual table with project_id and time to market column. i have created 2 measure that calculate the topthreshold/bottomthreshold  for 1% percentile BUT the main requirement here is that we need these measures to dynamically calculate those top/bottom percentiles whenever and filter is applied and not for the entire dataset.
Thus :

• TopThreshold =

           PERCENTILEX.INC(FILTER(ALLSELECTED(Projects),Projects[TIME_TO_MARKET_(TtM)] >= 0), Projects[TIME_TO_MARKET_(TtM)], 1- [Percentile] / 100)

  • BottomThreshold =

             PERCENTILEX.INC(FILTER(ALLSELECTED(Projects),Projects[TIME_TO_MARKET_(TtM)] >= 0), Projects[TIME_TO_MARKET_(TtM)], [Percentile] / 100)

    Next step is to create a calculated column that will work as flag for end user.
    I expect to get 1 when Time-to-Market > TopThreshold and Time-to-Market < BottomThreshold, 0 for all the records between the range.
    
    

    (TtM) Outlier Flag  =

    VAR CurrentTtM = Projects[TIME_TO_MARKET_(TtM)]

    VAR BottomThreshold = [BottomThreshold]

    VAR TopThreshold = [TopThreshold]

    RETURN

    IF(NOT ISBLANK(CurrentTtM) && (CurrentTtM < BottomThreshold || CurrentTtM > TopThreshold),1,0)

    (TtM) Outlier Flag 2 =

    VAR CurrentTtM = Projects[TIME_TO_MARKET_(TtM)]

    VAR BottomThreshold = [BottomThreshold]

    VAR TopThreshold = 1378.80

    RETURN

    IF(NOT ISBLANK(CurrentTtM) && (CurrentTtM < BottomThreshold || CurrentTtM > TopThreshold),1,0)

    
    However i notice that although measures give the correct result when used inside the visual table, the dont work properly when they are getting compared inside the calculated column. So when i compare the Time-to-Market with [TopThreshold] i dont get the expect result which i get when i compare Time-to-Market with 1378.80 (which is the expected numeric value for [TopThreshold] measure.
    

    

[bhanu_gautam · Accepted solution]

@ipezakas Create the measures for thresholds:

DAX
TopThreshold =
PERCENTILEX.INC(
FILTER(ALLSELECTED(Projects), Projects[TIME_TO_MARKET_(TtM)] >= 0),
Projects[TIME_TO_MARKET_(TtM)],
1 - [Percentile] / 100
)

BottomThreshold =
PERCENTILEX.INC(
FILTER(ALLSELECTED(Projects), Projects[TIME_TO_MARKET_(TtM)] >= 0),
Projects[TIME_TO_MARKET_(TtM)],
[Percentile] / 100
)

Create a measure for the outlier flag:

DAX
(TtM) Outlier Flag Measure =
VAR CurrentTtM = SELECTEDVALUE(Projects[TIME_TO_MARKET_(TtM)])
VAR BottomThreshold = [BottomThreshold]
VAR TopThreshold = [TopThreshold]
RETURN
IF(
NOT ISBLANK(CurrentTtM) &&
(CurrentTtM < BottomThreshold || CurrentTtM > TopThreshold),
1,
0
)

Add the (TtM) Outlier Flag Measure to your visual table. This measure will dynamically calculate the flag based on the current filter context applied to the visual.

[ipezakas]

Is it possible to make this work as a calculated column as well? Cause when i am trying to use it as filter inside a column chart the visuals breaks.