Counting an event occurring on concurrent days once

[ashamsuzzoha]

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.

[ryan_mayu · Accepted solution]

@ashamsuzzoha 

maybe you can try to create a new column and put that column in the visual

[ashamsuzzoha]

Thanks.  Yes, this does work provided I change the connection to my date table from [date] to [date2].

[ryan_mayu]

you are welcome