measure to count occurances between two dates

[smaur]

Ok, I've been working in DAX for several months now, but progress comes in fits and starts.
I really appreciated the help last time for this question:

http://www.mrexcel.com/forum/power-bi/928721-measure-selectively-sum-durations-events.html

Working on the same project, I have a table listing all system 'outage' events with a date the outage began and an outage category 'A', 'B' or 'C'. I also have a date calendar set up.

I would like to create a measure that based on a slicer selection of a desired date span it will return a count of the number of Category 'A' type outages between the dates selected by the user.

Thanks!

 

[Matt Allington]

If one event is one row in your table, then total events is
total events = countrows(eventsTable)

then

cat A events = calculate([total events],eventsTable[category]="A")

this will react to any date filters you apply

 

[smaur]

Thanks Matt, but that is not responding to my date slicer. It gives a total count of all Cat A events in the table.
The events fact table has a date for each event, on some days there are no events on others there can be multiple events.
The date slicer is tied to my dCalendar table which is not linked to the events table.

Hope that added info helps.

 

[Matt Allington]

Your date slicer needs to be on the calendar table, and the calendar table needs to be linked to your data table on the date column.

 

[smaur]

Matt, I really appreciate your help with this,

Yes the date slicer is on the calendar table, but I do not have it linked to the date field in the events table, the reason being that the field in the events table is actually the date and time (format mm/dd/yyyy hh:mm:ss) when the outage event started, while the calendar table is just the date in mm/dd/yyyy format.

I did not need the linking in order for the date span slicer to work with the measure that Ozeroth helped me create to calculate duration of the outages between two dates:

http://www.mrexcel.com/forum/power-bi/928721-measure-selectively-sum-durations-events.html

Can something similar be done here where I only need to acquire a count of the outages that have occurred between two dates selected on the slicer?

 

[Matt Allington]

There is always a way, but there are better ways and not so good ways. Relationships are there to help you, so unless there is a good reason, you should use it.

Best practice is to split the date from the time into 2 column a before loading into your database. You should do this before loading. Power Query is a great tool for this.

 

[smaur]

Ok, So I have added that relationship suggested by Matt, and sure enough I get the accurate count of the outages between the dates selected in the slicer, but in doing so, the measure I created as suggested by Ozeroth that tabulates the durations of those outages between the two dates ceases to function correctly, specifically for cases with a multi-day outage that commences or ends on a date outside the user selected slicer date range. In fact it ignores the tabulation of outage ours for any outage that has either a start date of an end date that breaches the selected date range.
I think I need to remove the relationship between the dCalendar and the outage event fact table and find a way to accurately tabulate the count of events matching my criteria that occur between the slicer selected date range

I really appreciate your guidance.

Again, here is the measure suggested by Ozeroth that accurately sums the durations of events occuring within the selected window as long as there is no relationship between calendar table and the event dates in the outage table.

http://www.mrexcel.com/forum/power-bi/928721-measure-selectively-sum-durations-events.html

I just need a companion measure that gives me a count of distinct outages (not number of days that a unit is in an outage state, but the actual count of distinct outage events).

-Steve

 

[Matt Allington]

Te easiest fix is to mark the relationship as inactive, and then call the relationship when you need it. This will make them all work. To call the relationship, add the following into the calculate as an additional filter.

USERELATIONSHIP(CalendarTableName,DataTableName)

 

[smaur]

That works great! Thanks so much.