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 👍
19 Replies
- DOLEARY85
Resident Rockstar
Hi,
For your closed cases use:
Measure = CALCULATE(count('Main Table'[Closed]),USERELATIONSHIP('Main Table'[Closed],'Date Table'[Date]))for open cases use:Measure 2 = CALCULATE(count('Main Table'[Opened]))If I answered your question, please mark my post as solution, Appreciate your Kudos 👍Hello DOLEARY85!
Unfortunately that didn't do it 😞
As you can see I filtered it on today's date, and there is a total of 27 tickets for today.
The bar chart looks like it is picking up the right number of opened tickets (as you suggested using measure:
mOpened2 = CALCULATE(COUNT('HELP DESK Ticket'[Open Date]))
But the number for Closed is way off at 59! It should only be 10 (I know there is only 9 in the picture above, but have to scroll for the 10th). This is the measure:
mClosed2 = CALCULATE(COUNT('HELP DESK Ticket'[Close Date]), USERELATIONSHIP('HELP DESK Ticket'[Close Date],'Date'[Date]))
Here's an image of the inactive relationship:
What am i missing??
Thank you in advance for all your help!
- DOLEARY85
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 👍
Alex_SawdoResolver II
I know this was probably solved already for you, but what you could do is disable ALL active relationships between your ticket and date table and use the USERELATIONSHIP function within each measure (one for closed and one for opened). This way, you can filter by date correctly.
Ohh! That's interesting. Follow-up: without an active relationship how would a date slicer work?? Thank you!!
- Alex_Sawdo
Because you "expose" the relationship within the measure, the date slicer will work as normal but will filter BOTH measures correctly and independently to one another. Think of the USERELATIONSHIP function like a "temporary relationship" for that specific context.
