Forum Discussion

bayremsyoud's avatar
bayremsyoud
Regular Visitor
1 year ago
Solved

Issue with Counting Tickets and Incidents Using Calendar Table

Hi everyone,

I'm facing an issue with counting tickets and incidents in Power BI. Here's my setup:

  • Tables:

    • Ticket Table: Columns - Created Date, Closed Date
    • Incident Table: Columns - Start Date, End Date

  • Calendar Table: Created from the minimum and maximum dates from both tables.

  • Relationships:

    • Active 1-to-many relationships:
      • Calendar[Date] -> Ticket[Created Date]
      • Calendar[Date] -> Incident[Start Date]
    • Inactive relationships:
      • Calendar[Date] -> Ticket[Closed Date]
      • Calendar[Date] -> Incident[End Date]

Despite this setup, when I use the Calendar[Date] column, the counts for created tickets and incidents (using their respective IDs) don't work. Additionally, when I select any month in the date slicer, it returns blank.

Any insights or solutions would be greatly appreciated!

Thanks!

Need Help
  • v-karpurapud's avatar
    v-karpurapud
    1 year ago

    Hi bayremsyoud 

    Welcome to the Microsoft Fabric Forum,
     

    Regarding the Issue with Counting Tickets and Incidents Using Calendar Table

     

    While I may not have full visibility into the specific structure of your dataset, I have created a sample .pbix file to demonstrate one possible approach to implementing the desired logic.

     

     I’ve included relevant screenshot and attached the .pbix file for your reference. Please take a moment to review them and see if this solution aligns with your requirements.

     


     

     

     

     



    If this doesn’t fully meet your needs, could you kindly share a sample of your data and more detailed context? That would help us provide a more accurate solution.

     

    If this response resolves your query, kindly mark it as Accepted Solution to help other community members. A Kudos is also appreciated if you found the response helpful.

     

    Thank You!

  • v-karpurapud's avatar
    v-karpurapud
    1 year ago

    Hello bayremsyoud 

    I have tried the following steps to address the issue:

    1. Create a calendar table generated from the MIN and MAX of all dates:

             Calendar Table:

    	Calendar = CALENDAR(
	    MINX(UNION(SELECTCOLUMNS(Ticket, "Date", Ticket[Created Date]), SELECTCOLUMNS(Incident, "Date", Incident[Start Date])), [Date]),
	    MAXX(UNION(SELECTCOLUMNS(Ticket, "Date", Ticket[Closed Date]), SELECTCOLUMNS(Incident, "Date", Incident[End Date])), [Date])
	)

     

         2. Create measures for Ticket Created, Ticket Closed, Incident Start Date, and Incident End Date:

           Tickets Created and Tickets Clsoed:
          

    Tickets Created
	
Tickets Created = 
	CALCULATE(
	    COUNT(Ticket[TicketID])  
	)
	
Tickets Closed
	
Tickets Closed = CALCULATE(
	    COUNTROWS(Ticket),
	    USERELATIONSHIP(Calendar[Date], Ticket[Closed Date])
	)


         Incidents Started and Incidents Ended:

        

    Incident Started	

Incidents Started = COUNTROWS(Incident)
	

Incident Ended	
	
Incidents Ended = CALCULATE(
	    COUNTROWS(Incident),
	    USERELATIONSHIP(Calendar[Date], Incident[End Date])
)

     

    Please use the above measures in the table visual.

    Additionally, it might be helpful to install the latest version of Power BI Desktop to ensure compatibility and access to the latest features. You can download the latest version from the official Power BI website.


    Thank You!

8 Replies

  • Deku's avatar
    Deku
    Super User
    1 year ago

    Split the datetimes in your ticket tables into separate date and time column. Then ensure the dates are cast as date in powerquery.

    • bayremsyoud's avatar
      bayremsyoud
      Regular Visitor
      1 year ago

      it's already splited and i only have the dates in those columns

  • v-karpurapud's avatar
    v-karpurapud
    Community Support
    1 year ago

    Hi bayremsyoud 

    Welcome to the Microsoft Fabric Forum,
     

    Regarding the Issue with Counting Tickets and Incidents Using Calendar Table

     

    While I may not have full visibility into the specific structure of your dataset, I have created a sample .pbix file to demonstrate one possible approach to implementing the desired logic.

     

     I’ve included relevant screenshot and attached the .pbix file for your reference. Please take a moment to review them and see if this solution aligns with your requirements.

     


     

     

     

     



    If this doesn’t fully meet your needs, could you kindly share a sample of your data and more detailed context? That would help us provide a more accurate solution.

     

    If this response resolves your query, kindly mark it as Accepted Solution to help other community members. A Kudos is also appreciated if you found the response helpful.

     

    Thank You!

    • bayremsyoud's avatar
      bayremsyoud
      Regular Visitor
      1 year ago

      Hello thanks for your detailed help and instrcuctions but the pdix won't open because of the Power BI version.

       

      • v-karpurapud's avatar
        v-karpurapud
        Community Support
        1 year ago

        Hello bayremsyoud 

        I have tried the following steps to address the issue:

        1. Create a calendar table generated from the MIN and MAX of all dates:

                 Calendar Table:

        	Calendar = CALENDAR(
	    MINX(UNION(SELECTCOLUMNS(Ticket, "Date", Ticket[Created Date]), SELECTCOLUMNS(Incident, "Date", Incident[Start Date])), [Date]),
	    MAXX(UNION(SELECTCOLUMNS(Ticket, "Date", Ticket[Closed Date]), SELECTCOLUMNS(Incident, "Date", Incident[End Date])), [Date])
	)

         

             2. Create measures for Ticket Created, Ticket Closed, Incident Start Date, and Incident End Date:

               Tickets Created and Tickets Clsoed:
              

        Tickets Created
	
Tickets Created = 
	CALCULATE(
	    COUNT(Ticket[TicketID])  
	)
	
Tickets Closed
	
Tickets Closed = CALCULATE(
	    COUNTROWS(Ticket),
	    USERELATIONSHIP(Calendar[Date], Ticket[Closed Date])
	)


             Incidents Started and Incidents Ended:

            

        Incident Started	

Incidents Started = COUNTROWS(Incident)
	

Incident Ended	
	
Incidents Ended = CALCULATE(
	    COUNTROWS(Incident),
	    USERELATIONSHIP(Calendar[Date], Incident[End Date])
)

         

        Please use the above measures in the table visual.

        Additionally, it might be helpful to install the latest version of Power BI Desktop to ensure compatibility and access to the latest features. You can download the latest version from the official Power BI website.


        Thank You!

  • v-karpurapud's avatar
    v-karpurapud
    Community Support
    1 year ago

    Hi bayremsyoud 

    May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

    Thank you.

  • v-karpurapud's avatar
    v-karpurapud
    Community Support
    1 year ago

    Hi bayremsyoud 

    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

    Thank you.

  • v-karpurapud's avatar
    v-karpurapud
    Community Support
    1 year ago

    Hi bayremsyoud 

    I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

    Thank you.