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.
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!
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 |
---|---|
96 | |
95 | |
82 | |
71 | |
64 |
User | Count |
---|---|
115 | |
105 | |
95 | |
79 | |
72 |