Linking Dates

[micklowe]

Hi Everyone

I am trying to build a time based report to show tickets opened vs tickets closed. I have a date table and a ticket table and I know I cannot have active joins on the create_date and date_complete concurrently.

I found a post suggesting I use 'USERELATIONSHIP' so I created a measure as follows:

Tickets Created = CALCULATE(DISTINCTCOUNT('wh_task'[task_id]),USERELATIONSHIP('wh_task'[create_time], 'DateTable'[Date]))

If I add the date to a table visual I get the expected result, if I then drop the measure on I just get a total count of tickets:

A simplified example of my fact table looks like this:

Data						Figures on Graph
task_id	create_time	completed_date			Created	Completed
1	01/01/2025	03/01/2025				2	0
2	01/01/2025	03/01/2025				2
3	03/01/2025	03/01/2025				1	3
4	05/01/2025					2	1
5	05/01/2025	05/01/2025				2	1
6	08/01/2025	10/01/2025				2	0
7	08/01/2025					2	0
8	09/01/2025					1	0

I have got something working on a different report but I had to load duplicate tables and create two calendar tables which to me seems messy as I had to create heidden synced sliders and the visuals work independantly.

Can anyone suggest a better solution or advise why the measure doesn't work?

Thanks 

Mick

[Anonymous · Accepted solution]

Hi,

Looks like no dates are being pulled into your table visualization.

Maybe check the following things:

1) the relationship is between the Date table [Date] and the two date fields in your task table

2) the date field in the table visual is from the calendar table

Here is an idea that might help too:

Add a filter to evaluate null completion dates so you get the correct count of completed tasks in your visualization table. See the results in my screen shot of A vs B.   I'd assume you want the B to be shown so no blank row and correct count of completed tasks. 

[micklowe]

After a bit more digging, I worked out both these solutions work, my date table problem was down to the date format on the ticket table, once I changed this in the query the links started working.

Many thanks @DataNinja777 & @Anonymous 

[DataNinja777 · Accepted solution]

Hi @micklowe ,

You're on the right track using USERELATIONSHIP, but the issue with your measure is that the date table is only filtering one relationship at a time. When you drop the measure into a table visual, the relationship may not be applied dynamically as expected. Instead of creating duplicate tables, you can define two separate measures—one for tickets created and one for tickets completed—both leveraging USERELATIONSHIP.

For tickets created, the measure should be:

Tickets Created = 
CALCULATE(
    DISTINCTCOUNT('wh_task'[task_id]),
    USERELATIONSHIP('wh_task'[create_time], 'DateTable'[Date])
)

For tickets completed, the measure should be:

Tickets Completed = 
CALCULATE(
    DISTINCTCOUNT('wh_task'[task_id]),
    USERELATIONSHIP('wh_task'[completed_date], 'DateTable'[Date])
)

Now, when you place DateTable[Date] in a table visual alongside these two measures, Tickets Created will use the relationship with create_time, while Tickets Completed will use the relationship with completed_date. This approach ensures that both types of data are correctly counted without duplicating tables.

If the totals in your table do not show the expected values, you can add REMOVEFILTERS to ensure that the measure calculates correctly across the dataset:

Tickets Created =
CALCULATE(
    DISTINCTCOUNT('wh_task'[task_id]),
    USERELATIONSHIP('wh_task'[create_time], 'DateTable'[Date]),
    REMOVEFILTERS('DateTable')
)

Another approach to simplifying the model is to restructure the data by creating an event-based table, where all ticket dates are appended into a single column with an event type flag (Created/Completed). This method avoids multiple relationships and allows for a single date table. The transformation can be done with a UNION operation:

Event Date = 
UNION(
    SELECTCOLUMNS('wh_task', "Date", 'wh_task'[create_time], "Event", "Created"),
    SELECTCOLUMNS('wh_task', "Date", 'wh_task'[completed_date], "Event", "Completed")
)

Using this event-based approach, a simple COUNTROWS measure can be applied with a filter on the event type. If your visual is still not behaving as expected, ensure that the DateTable is marked as a date table and that no filters or slicers are interfering with the calculation.

Best regards,

[micklowe]

Hi @DataNinja777 

Thanks for your quick response, I think the measure looks like what I already had, I've copied these in but still get the same result. I've tried adding the meaure to both tables but get the same result.

[Anonymous · Accepted solution]

Hi,

Looks like no dates are being pulled into your table visualization.

Maybe check the following things:

1) the relationship is between the Date table [Date] and the two date fields in your task table

2) the date field in the table visual is from the calendar table

Here is an idea that might help too:

Add a filter to evaluate null completion dates so you get the correct count of completed tasks in your visualization table. See the results in my screen shot of A vs B.   I'd assume you want the B to be shown so no blank row and correct count of completed tasks. 

[micklowe]

@DataNinja777 @Anonymous I think the table joins are the problem, when returning data to a grid the date fields are all blank:

The join looks correct to me?

Thanks

[micklowe]

Thanks @Anonymous I think you might be on the right lines, so if I'm reading this correctly, my screenshot is showing a count of all records in the 'task' table, 67,419 is prossibly the number of records, against each date as opposed to filtering that number down by individual date?

You are correct about the NULL dates, I do not want to add NULL completed dates to that total, created dates should all be populated.