I have a data table that captures information on events, using three key columns to identify events by their "Incident Date", "Incident Name", and "Space Name". Using dax in Power BI, I want to count the number of distinct "Incident Names" in a defined date range.
I've tried to do this using the following dax query:
MonthX Unique Events= CALCULATE(DISTINCT('Incident Tracker'[Incident Name]),DATESBETWEEN('Incident Tracker' [Incident Date],01/01/24,01/10/24))
However, Power BI will not let me use DATESBETWEEN as the data table is not a date table.
Attempting to convert the table to a date table fails because there are days when a single incident affects multiple different spaces, leading to multiple different entries in the table under the same date. Another reason is that multiple incidents can sometimes occur on the same date.
| Incident Date | Incident Name | Space Name |
|---|---|---|
| 01/01/24 | Snowball fight | Mess hall |
| 01/01/24 | Snowball fight | Lounge |
| 01/01/24 | Blizzard | Garden |
| 01/10/24 | Ice storm | Lounge |
| 01/10/24 | Ice storm | Mess hall |
| 01/10/24 | Ice storm | Lounge |
The number of unique incidents from 01/01/24 - 01/10/24 would be 3 in this case^.
Any advice for how to get the count of unique incidents using a dax query?
