Forum Discussion

DrEvil's avatar
DrEvil
Frequent Visitor
8 years ago
Solved

Diferent times in same row. Issue creeating graphs

 

Hi, i have an Excel Sheet with a list of 'Issue Id' with the 'created' date and the 'resolved' date. I need to create a graph in Power Bi that shows how many Issues are CREATED each time period and how many are RESOLVED. The issue is that i cant get Power BI to recognize both dates, and it links both events (created and resolved) to the same date. 

In the screenshots:

  • What i have in the excel Sheet
  • The desired output done through excel dynamic tables
  • What im getting in Power BI

  • Thejeswar's avatar
    Thejeswar
    8 years ago

    DrEvil,

    For getting no of Issue Created, you will have the following

     

    Category - Month

    Value - Issue ID

     (The Above thing is already in place with you)

     

    For Getting count of no of Issues Resolved, you the below calculated column

     

    Count of Resolved IssueID = CALCULATE(COUNT(Table2[issue ID]),USERELATIONSHIP(Table2[Resolved], Dates[Date]))

    Here Table2 is my actual Data Table and Dates is my Date Table with All Dates

     

    Hope this solves your issue!!!

  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi,

     

    The formula is great. Just a tip, it should be a measure.

     

    Best Regards,

    Dale

4 Replies

  • Thejeswar's avatar
    Thejeswar
    Icon for Super User rankSuper User
    8 years ago

    DrEvil,

    I think in the graph shown below from power bi, you are using a month column and that column is calculated based on one of the dates (probably Created Date)

     

    This should be the reason why your graph is not detecting the other date avaialbe

    • DrEvil's avatar
      DrEvil
      Frequent Visitor
      8 years ago

      Hi Thejeswar Thank you for your answer, this is what i tried: i created an Excel sheet with just a list of dates and linked both dates from the Sheet with all the information. The 'Fecha' shown in the first screenshot is the one from the sheet with just dates.

      Im pretty new to Power Bi so im a bit lost atm.

      • Thejeswar's avatar
        Thejeswar
        Icon for Super User rankSuper User
        8 years ago

        DrEvil,

        For getting no of Issue Created, you will have the following

         

        Category - Month

        Value - Issue ID

         (The Above thing is already in place with you)

         

        For Getting count of no of Issues Resolved, you the below calculated column

         

        Count of Resolved IssueID = CALCULATE(COUNT(Table2[issue ID]),USERELATIONSHIP(Table2[Resolved], Dates[Date]))

        Here Table2 is my actual Data Table and Dates is my Date Table with All Dates

         

        Hope this solves your issue!!!