Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I have the following tables in my data source.
Element
ElementId | ParentElementId | ElementLevel | ElementName |
1 | 0 | 1 | BB |
2 | 1 | 2 | VS |
3 | 1 | 2 | VD |
4 | 1 | 2 | 22 |
5 | 2 | 3 | VSCS |
6 | 2 | 3 | VSF |
7 | 3 | 3 | VCS |
8 | 3 | 3 | VF |
9 | 4 | 3 | 22CS |
10 | 4 | 3 | 22F |
... | ... | ... | ... |
ElementValueShift
ElementId | Value | ShiftTime |
2 | 76 | 2019-09-28 06:00 |
2 | 50 | 2019-09-28 18:00 |
2 | 44 | 2019-09-29 06:00 |
2 | 12 | 2019-09-29 18:00 |
2 | 78 | 2019-09-30 06:00 |
2 | 31 | 2019-09-30 18:00 |
3 | 67 | 2019-09-28 06:00 |
3 | 5 | 2019-09-28 18:00 |
3 | 44 | 2019-09-29 06:00 |
3 | 21 | 2019-09-29 18:00 |
3 | 87 | 2019-09-30 06:00 |
3 | 13 | 2019-09-30 18:00 |
... | ... | ... |
These two tables are linked by ElementId. Within table Element, the child element is linked to the parent element using ElementId-ParentElementId.
What I would like to do is to calculate the four-shift moving average for each element within each level, where the result will be something like this:
ShiftTime | ElementId | MovingAverage |
2019-09-28 06:00 | 2 | 45.5 |
2019-09-28 18:00 | 2 | 46 |
2019-09-29 06:00 | 2 | 41.25 |
2019-09-28 06:00 | 3 | 34.25 |
2019-09-28 18:00 | 3 | 39.25 |
2019-09-29 06:00 | 3 | 41.25 |
... | ... | ... |
I tried the following:
MovingAverage =
Would it simplify things if you gave each element a composite key that included its parents, like "6-2-1" and then had a level column 3. When you calculate the average it seems like all you care about is the level. To associate an element with a shift/value you just need to identify the precise element, which a composite key would do.
Help when you know. Ask when you don't!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |