Forum Discussion
Join Calendar table on 2 dates
Hello Everyone,
I'm trying to do a Histogram chart for my company like this :
On this one, the dates comes from my calendar table.
My data model looks like this :
But now, I cannot rely the 2 Incidents tables to each other on the incident ID.
My idea, is very simple, I want to show on the chat, how many incident where created on a specific day and how many were close on the same day. It's working fine but if I want to filter by "Personne traitant l'incident" (for exemple), the closing incident don't move.
I have tryed to rely my the two incidents tables on the Incident_id but my chart is not good anymore because all the dates will be the CreatedDate and not the Created and Closing Date
Do you have any idea how can I solve this ?
(I can rebuilt my resquests on SQL if needed)
Hi Hugz02 ,
For this you need to do two relationships with the calendar table:
Date - Open Date
Date - Closed Date
One of this relationships will be inactive so for that one you need to use a USERELATIONSHIP to activate it let's assume that the open date is the active relationship then for the closed you would need to do the following measure:
Closed incidents = CALCULATE(COUNT(Table[Incident]), USERELATIONSHIP(Calendar[Date], Table[CloseDate]))
2 Replies
Hi Hugz02 ,
For this you need to do two relationships with the calendar table:
Date - Open Date
Date - Closed Date
One of this relationships will be inactive so for that one you need to use a USERELATIONSHIP to activate it let's assume that the open date is the active relationship then for the closed you would need to do the following measure:
Closed incidents = CALCULATE(COUNT(Table[Incident]), USERELATIONSHIP(Calendar[Date], Table[CloseDate]))
