How to make a dynamically calculated column (or equivilant measure) based on visual filter?

[mithrandir]

So, I'm using Power BI to measure my teams JIRA kanban data; for those who aren't familiar with JIRA, it's an issue tracking tool commonly used for software development. The functionality I'm trying to create is giving every row in an 'Issues' table a rank based on standard deviation based on the time it took to resolve the issue. Then be able to filter my visual by a date value and have the rank for each row dynamically change for a new standard deviation taken from the given time frame.

sdRankTotalDaysSpent = 
VAR mean = AVERAGE(GetIssues[totalDaysSpent])
VAR stdD = STDEV.P(GetIssues[totalDaysSpent])
RETURN
SWITCH(
    TRUE(),
    [totalDaysSpent] > (mean + (3 * stdD)),
    7,
    [totalDaysSpent] <= (mean + (3 * stdD)) &&
        [totalDaysSpent] > (mean + (2 * stdD)),
    6,
    [totalDaysSpent] <= (mean + (2 * stdD)) &&
        [totalDaysSpent] > (mean + stdD),
    5,
    [totalDaysSpent] <= (mean + stdD) &&
        [totalDaysSpent] > mean,
    4,
    [totalDaysSpent] <= mean &&
        [totalDaysSpent] > (mean - stdD),
    3,
    [totalDaysSpent] <= (mean - stdD) &&
        [totalDaysSpent] > (mean - (2 * stdD)),
    2,
    [totalDaysSpent] <= (mean - (2 * stdD)) &&
        [totalDaysSpent] > (mean - (3 * stdD)),
    1,
    [totalDaysSpent] <= (mean - (3 * stdD)),
    0)

The problem with creating the column above is that the values are stored, so even though my count of each rank can be filtered, the ranks themselves do not change based on a new standard deviation. I've created measures for the mean and standard deviation so that they are dynamic, but i'm having trouble translating that to each row.

Any help is much appreciated!

[Phil_Seamark]

HI @mithrandir

IT should be possible to generate a measure to do what you need.  And a measure will react to filters etc.

If you post a small sample of your data, including a mockup of what you expect your results to look like, we can try and help you out.

[mithrandir]

For the sake of discussion, I created mock data to illustrate what I care about.

So, for each row, I currently have a column that simply gives the row a rank from 0-7 based on the number of standard deviations from the mean (left or right respectively). That calculated column formula is the one on the original post. The problem is, on a page in my report, if I add a filter slider by createDate then the counts of each rank (say in a histogram) adjust based on my filter, but the standard deviation and mean are not recalculated. Creating measures for the mean and standard deviation are the obvious fix for that, but I didn't know how to keep the rank column and have the value change dynamically or put it into a measure that evaluates for each column.

Thanks for the quick response @Phil_Seamark!

[Eric_Zhang]

@mithrandir

It appears that a measure like below would work.

sdRankTotalDaysSpent Measure = 
VAR mean = CALCULATE(AVERAGE(GetIssues[totalDaysSpent]),ALLSELECTED(GetIssues))
VAR stdD = CALCULATE(STDEV.P(GetIssues[totalDaysSpent]),ALLSELECTED(GetIssues))
VAR totalDays = SUM(GetIssues[totalDaysSpent])
RETURN
SWITCH(
    TRUE(),
    totalDays > (mean + (3 * stdD)),
    7,
    totalDays <= (mean + (3 * stdD)) &&
        totalDays > (mean + (2 * stdD)),
    6,
    totalDays <= (mean + (2 * stdD)) &&
        totalDays > (mean + stdD),
    5,
    totalDays <= (mean + stdD) &&
        totalDays > mean,
    4,
    totalDays <= mean &&
        totalDays > (mean - stdD),
    3,
    totalDays <= (mean - stdD) &&
        totalDays > (mean - (2 * stdD)),
    2,
    totalDays <= (mean - (2 * stdD)) &&
        totalDays > (mean - (3 * stdD)),
    1,
    totalDays <= (mean - (3 * stdD)),
    0)

[mithrandir]

This works perfectly! I'm unclear on why you use SUM() for totalDays though. I assume it's only being evaluated for one row at a time?

Thanks again @Eric_Zhang!

UPDATE: This works in a table visual like the one above, but you can't add a measure as the axis on a histogram and this measure will evaluate to seven for all of the column counts if you use it for the value. Ultimately, this is what I need to accomplish. The table was just an intermediate step to illustrate the problem.

Thanks for the quick reply @Eric_Zhang

[Eric_Zhang]

---

@mithrandir wrote:

This works perfectly! I'm unclear on why you use SUM() for totalDays though. I assume it's only being evaluated for one row at a time?

Thanks again @Eric_Zhang!

UPDATE: This works in a table visual like the one above, but you can't add a measure as the axis on a histogram and this measure will evaluate to seven for all of the column counts if you use it for the value. Ultimately, this is what I need to accomplish. The table was just an intermediate step to illustrate the problem.

Thanks for the quick reply @Eric_Zhang

---

What is the axis when the measure is used as a value? It indeed has difference between table and distogram visuals, as the evaluate context may vary.

[mithrandir]

I'd like the axis to be the values of the measure (0-7) and the values to be a count of each that can be filtered by a date range.

Thanks @Eric_Zhang!

[mithrandir]

So, I just wanted add an update on this just in case anyone else has had the same issue. I've created a different table w/relationship for each time range I would be interested in (3 months, 6 months, 1 year, 2 years, All Time) and then a table containing a column of intervals. Then I created a measure in the parent table to switch on the name of the interval.

switchOnIntervalSDR = 
SWITCH(
    FIRSTNONBLANK(GetDateInterval[interval], GetDateInterval[interval]),
    "3 Months",
    COUNT(GetJIRAsdr3Months[sdRank]),
    "6 Months",
    COUNT(GetJIRAsdr6Months[sdRank]),
    "1 Year",
    COUNT(GetJIRAsdr1Year[sdRank]),
    "2 Years",
    COUNT(GetJIRAsdr2Years[sdRank]),
    "All Time",
    COUNT(GetJIRAsdrAllTime[sdRank])
)

This can be put in the values field for a histogram and then I added the interval column to a slicer. This effectively gives me what I wanted to achieve, but it seems that it can only be done with static data. If anyone figures out a more elegant solution it'd be much appreciated.

[Phil_Seamark]

Hi @mithrandir

I should be able to look at this later today for you 🙂