Counts number of Incidents with a Date slicer

DAX DAX Calculations
1

Hello, I am trying to count number of incidents with a date slicer. I have an inactive relationship with the date slicer and the incident date.

My calculations are the following:

Incidents = CALCULATE(COUNT(tIncidents[INCIDENT_ID]),FILTER(ALL(tIncidents),tIncidents[INCIDENT_DATE] = MAX(Calendar[Date])))

However, when I try to use the Date slicer to see the numbers, they are coming out incorrectly. Could somebody tell me what I might be doing wrong here?

2

Hi @supergallagher25. Would you have a sample PBIX or Excel dataset to work from? As well, could you provide a mock-up of your desired outcome (PowerPoint, Excel, sketch)? Thanks, Greg

3

Just a thought - but if the relationship is inactive, don’t you need to activate it in your measure - with something like USERELATIONSHIP?

4

Hi @supergallagher25 , we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. Here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.
  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

5

Hi @supergallagher25,

I will sugest you check the time inteligence function video here, it will hepl you in settling your model very well https://portal.enterprisedna.co/courses/enrolled/108877

DAVID.

6

Hello Greg,

I apologize for the late response, I had something personal going on. Thank you so much for trying to help me out. I am attaching a sample file here. Sample.pbix (53.7 KB)

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
8

Thank you so much for the help. The video tutorials are also pretty great as a reference.

9

Hello @supergallagher25,

You’re Welcome. :slightly_smiling_face:

I’m glad I was able to help you.

Thanks & Warm Regards,
Harsh