Trying to create a measure for counting dates from two different fields.

[rudyCoastal]

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!

[Alex_Sawdo]

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. 

[rudyCoastal]

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. 

[rudyCoastal]

So as soon as I set both relationships to inactive, all the visuals started showing "all of the data" and any changes to the date slicer seemingly had no effect. What did I break? LoL 

[Alex_Sawdo]

How are your measures set up? Both of them should be using a CALCULATE and a COUNT function with the USERELATIONSHIP function to enable the relationship to the date dimension for each measure. You will need to have a measure within each visual for the date slicer to filter correctly, and the column in your date slicer has to be sourced from your date table. 

[rudyCoastal]

Good morning Alex,

Thank you so much for the reply! As Doleary85 helped me out with, one of the measures looks like this:
mClosed2 = CALCULATE(COUNT('HELP DESK Ticket'[Close Date]), USERELATIONSHIP('HELP DESK Ticket'[Close Date],'Date'[Date]))

And having just tested it, yeah! I see that it's working! Thank you so much for your help! I very much appreciate it!

[DOLEARY85]

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 👍

[rudyCoastal]

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 · Accepted solution]

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 👍

[rudyCoastal]

As you've probably noticed I'm pretty new to PBI. Would you mind explaining why one worked and the other didn't?? Thank you for your help!! Very much appreciated!!!

[DOLEARY85]

Yeah, no problem.

The opened cases worked as that's your active relationship so filtering the information will already take that into account. Therefore all you needed was a count.

The closed one needed to activate the inactive relationship to start the count based on that link to the date table. 

hope that makes sense, if you need more info on this feel free to private message me  

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[rudyCoastal]

Actually, one more wrinkle....

So I filted the date to 4/5/2022. Looks like 21 tickets were open that day, and actually 0 were closed. And one of those wasn't closed until 6 months later!

Thoughts? Sorry to keep bugging you! I very much appreciate the help!

[DOLEARY85]

Based on my testing my first measure for Closed count should work better than the 2nd one,  could you retry it?

[rudyCoastal]

Could the issue stem from my date slicer on top? It is using the Date.Date field (which has an active relationship to the MainTable.Open Date field).

Reverting the mClosed back to your original suggestion shows me the following, which is strangely hit and miss:

So If i use 3/1/2023:

The closed count should be 1, but shows 5!

and using 3/1/2022

The numbers are fine!

So strange! Thank you!

[DOLEARY85]

Also it might be worth changing your date table, date field to date rather than date time

[rudyCoastal]

Just did that, and didn't seem to make much of a difference.

I add the measured columns to a table visualization, and got the below. Not sure if that helps at all?

EDIT: So yeah, it looks like it is work like it should.... *sigh*. Glad I dropped those fields in. So this is good, i think...?

[DOLEARY85]

That is really odd, are there any filters on the table? any blank closed fields?

[DOLEARY85]

No worries,

That's strange, i don't suppose you are able to share the PBI file or the data for me to look at? it'll just make it easier than recreating it 

[rudyCoastal]

Using the transform data button I've deleted some tables that might sensitive or identifying, but is there any way to "flush" them from the pbix file?

Thank you!!