Forum Discussion
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.
- BottomThreshold =
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
- bhanu_gautamSuper User
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.
- ipezakasHelper 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.