Counts number of Incidents with a Date slicer

DAX DAX Calculations
7

Hello @supergallagher25,

Thank you for posting your query onto the Forum.

Just for the reference I’ve changed the relationship in your file which was one-to-one and bi-directional to One-to-Many and single directional as per the fundamentals. Below is the screenshot attached for the reference.

Count no. of incidents - 1
Count no. of incidents - 11254×795 76.4 KB

Now, for calculation purpose you can use the formula as shown below since you’ve an inactive relationship. You can turn on this inactive relationship by using the USERELATIONSHIP function.

Incidents Count 2  = 
CALCULATE( COUNT( tIncidents[INCIDENT_ID] ) , 
    USERELATIONSHIP( tDate[Date] , tIncidents[INCIDENT_DATE] ) )

Once you use this formula, you can have the desired result that you’re looking for.

Attached is the screenshot for the reference.

Count no. of incidents - 2
Count no. of incidents - 2945×673 39.2 KB

I’m also attaching the working of my PBIX file for the reference and also providing few of the video links about how you can handle the inactive relationship and use the USERELATIONSHIP function.

Hoping you find this useful. :slightly_smiling_face:

Thanks & Warm Regards,
Harsh

Sample.pbix (59.2 KB)

1 Like