PowerBi: Measure over ID

Question

I have a table with:
ID, delta & Date

I added a calculated column:

SD = CALCULATE(STDEV.P(Query1[delta]),FILTER(Query1,Query1[sys_id] = EARLIER(Query1[sys_id])))

Calculating the standard deviation of delta... for each id.

What I want to do now is that when I have a filter (slicer) on the date column that it doesn't take those rows into account when calculating SD

So I want a Measure instead of a calculated column but the earlier function doesn't work then.

I'm a complete beginner and have googled a lot but can't seem to figure out how it works. How can I filter in the measure to only take the rows with the same ID when calculating?

Many thanks for any pointers in the right direction.

EDIT: I oversimplified I think. Currently, I have the following Graph:

This only shows the values for which a 'relative' significant changes have occurred (delta is 'abnormal'), for that day, for that ID. This I accomplish by filtering the graph where Significance > 1

Significance = ABS(AVERAGE(Query1[delta])) / (AVERAGE(SignificanceLevel[SignificanceLevel]) * AVERAGE(Query1[SD]))

Now my SD is simply calculated as stated above, but I want this to take into account the period I am looking at (data slicer). Because for example further into the past, the situation was worse and changes that are now significant weren't then.

But I still look at the value of each day separately in the graph (and will always have to) so a simple measure will always return 0 for SD for this day as it only looks at that specific day (= 1 row only) and not to all days I specified in my data slicer.

So is that chart working as intended and you just want to be able to adjust the date range? If not, how do you want it to work? What do the colors represent on the chart? Can you create a minimal example to help explain? — Alexis Olson
Yes it works as intended, each color would be a different id. — Dextrious
Yes it works as intended, each color is a different id. So I can adjust the date range in the representation of the 'significant' changes per id with the daye slicer..but that doesn't affect my 'significant' parameter as it currently takes the whole period (calculated column) and when I try to make it a measure it will always return 0 as it evaluates only for that specific date.. while I want it to be evaluated for the whole range(as in slicer) (I fear what I'm trying to do is impossible and that I will need to be looking at something like a moving standard deviation? ..just invented that) — Dextrious
So you want SD fixed, but the Significance to be rolling? — Alexis Olson
SD and thus also Significance rolling. This only evaluated over the period specified in the dateslicer...and for a single date in the graph this should still come out as seen over the whole period. ( As one date = 1 row doesn't make any sense to look for significance) — Dextrious

Alexis Olson Alexis Olson · Accepted Answer · 2018-03-02T16:12:03

The EARLIER function isn't usually what you want inside of a measure. If you have a visual that breaks out the ID values, a measure should automatically use that filter context and you don't need to put a filter in your calculation.

Try using the following in a matrix visual that has ID on the rows or columns:

SD = STDEV.P(Query1[delta])

It should interact fine with a Date slicer.

If I'm understanding your comment correctly, then I believe you want something more along the lines of the following:

SD = CALCULATE(STDEV.P(Query1[delta]), ALLSELECTED(Query1[Date]))

This should give you the standard deviation over all selected dates from your date slicer.

PowerBi: Measure over ID

1 Answers