Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Hugz02
Frequent Visitor

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. 

2023-12-08_13h59_00.png


My data model looks like this : 

2023-12-08_14h00_07.png


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)



1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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]))

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
Hugz02
Frequent Visitor

Hi @MFelix . Thx a lot for your answer, it works perfectly. 

MFelix
Super User
Super User

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]))

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.