Forum Discussion
Trying to create a measure for counting dates from two different fields.
Good afternoon all,
I have a table that looks a lot like the example below. Also, I have a date dimension table that has an active relationship to the Opened field and an inactive relationship to the Closed field.
From the data below, I would like to create a bar chart that has the months along the X access, and a count of tickets along the Y. For example, based on the data below, we would like two bars coming up from Jan 2023. One bar for Opened (equals 2) and the another for Closed (=2). Then for Feb 2023, again two bars: Opened (=3) and Closed (=1). Finally for Mar 2023, two bars: Opened (=2) and Closed (=3).
I'm figuring I have to do this with DAX Measures, but I've hit a wall and any help would be appreciated!
| Opened | Closed | Tech | Ticket Desc |
| 1/2/2023 | 1/5/2023 | Don | Broken mouse |
| 1/4/2023 | 1/15/2023 | John | Broken monitor |
| 2/20/2023 | 2/21/2023 | John | Need update |
| 2/25/2023 | 3/4/2023 | Don | Do I need to reboot? |
| 2/25/2023 | 3/5/2023 | Don | Screen flicker |
| 3/1/2023 | 3/2/2023 | John | Need PC |
| 3/6/2023 | John | How do I? |
Thank you in advanced!
Ah okay you want it to also filter out based on the active relationship, you just need to add the other relationship as a filter too, try:
mClosed2 = CALCULATE(COUNT('HELP DESK Ticket'[Close Date]), USERELATIONSHIP('HELP DESK Ticket'[Close Date],'Date'[Date]),USERELATIONSHIP('HELP DESK Ticket'[Open Date],'Date'[Date]))
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
