Linking Dates

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. 

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,