Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I need some help writing a measure to calculate the number of events by day and plotting it by date. This is a sample of my table:
date | unique_id | event |
6/24/2022 | 1005022_1191625136_119162520_18934250 | 1 |
6/26/2022 | 1005022_1191625136_119162520_18934250 | 2 |
6/27/2022 | 1005022_1191625136_119162520_18934250 | 2 |
7/9/2022 | 1005022_1191625136_119162520_18934250 | 3 |
7/10/2022 | 1005022_1191625136_119162520_18934250 | 3 |
7/11/2022 | 1005022_1191625136_119162520_18934250 | 4 |
7/12/2022 | 1005022_1191625136_119162520_18934250 | 4 |
7/13/2022 | 1005022_1191625136_119162520_18934250 | 5 |
7/14/2022 | 1005022_1191625136_119162520_18934250 | 5 |
The measure I've written is:
Total L2 Charging Events Detected (2) =
COUNTROWS(
DISTINCT(
SELECTCOLUMNS(
SUMMARIZE(
FILTER(l2_ami,l2_ami[event] <> 0),
l2_ami[unique_id],
l2_ami[event],
l2_ami[date],
"id_event_ky", CONCATENATE(CONCATENATE(l2_ami[unique_id], "_"), l2_ami[event])
),
"_ky", [id_event_ky]
)
)
)
The measure works in total, but when I try in plot it against date, i'm running into the problem that some events span two days. We only want to count the event on the first day. So in June, there should be two events, one on 6/24 and 6/26. But when I plot against Date, it has events on 6/24, 6/26, and 6/27.
Any help would be appreciated.
Solved! Go to Solution.
maybe you can try to create a new column and put that column in the visual
Proud to be a Super User!
maybe you can try to create a new column and put that column in the visual
Proud to be a Super User!
Thanks. Yes, this does work provided I change the connection to my date table from [date] to [date2].
you are welcome
Proud to be a Super User!
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |