Forum Discussion

ipezakas's avatar
ipezakas
Helper I
1 year ago
Solved

Measure doesnt work as expected inside VAR

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.

       

       

  • 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.

     

2 Replies

  • 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's avatar
      ipezakas
      Helper I

      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.