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.
Hey,
I need to standardize my data. I get at irregular intervals a value for my devices. Message is sent when status changes. Here is an example data and what I mean by standardizing. In this example is to convert time to 10min sections.
10A | Time | 10:11 | 10:14 | 10:18 | 10:48 | |||
Value | 1 | 0 | 1 | 0 | ||||
20B | Time | 10:18 | 10:34 | 10:51 | ||||
Value | 0 | 1 | 0 | |||||
Would look like this if "cumulative" in 10min sections-> | ||||||||
10A&20B | Time | 10:10 | 10:20 | 10:30 | 10:40 | 10:50 | ||
Cumulative value | 1 | 1 | 2 | 1 | 0 |
Here is how the data looks like:
Time | Device | Value |
28.3.2018 10:11 | 10A | 1 |
28.3.2018 10:14 | 10A | 0 |
28.3.2018 10:18 | 10A | 1 |
28.3.2018 10:48 | 10A | 0 |
28.3.2018 10:18 | 20B | 0 |
28.3.2018 10:34 | 20B | 1 |
28.3.2018 10:51 | 20B | 0 |
How do I write a Measure to convert the data into a table visual like this?
Time | Cumulative value |
28.3.18 10:10 | 1 |
28.3.18 10:20 | 1 |
28.3.18 10:30 | 2 |
28.3.18 10:40 | 1 |
28.3.18 10:50 | 0 |
Thanks!
Hi @Ikeumlaut,
One concern:
How to group those time values based on 10 min interval?
Why is 10:11 for 10A matched with 10:18 for 20B? Why should these two values belong to 10:10? While 10:14 for 10A and 10:18 for 10A belong to 10:20 and 10:30 section.
Best regards,
Yuliana Gu
Hey @v-yulgu-msft
sorry, that is a mistake on the chart. I edited my post and it messed up it. So the grouping is:
Best,
Ike
Hi @Ikeumlaut,
I am still confused about this calculation. That case, the Cumulative value for "28.3.18 10:10" in result table should be 2 rather than 1. Also, the Cumulative value for "28.3.18 10:20" in result table should be 0.
Regards,
Yuliana Gu
Hey,
I do not want the sum of the values. Instead the cumulative distinct count of devices that have sent a value of 1. So I want to know how many devices have a value of 1 at each time interval. Sometimes the device sents the value at the interval, but sometimes it has sent the message right before the interval so I need to take that into account somehow.
Thank you for helping out!
Best,
Ike
Hey @v-yulgu-msft,
here is a picture of what I'm looking for. Hope this explains it better.
Best,
Ike
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 |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |