Forum Discussion

cearly65's avatar
cearly65
Frequent Visitor
4 years ago
Solved

Filterable Unresolved Issues Chart for JIRA data

I am trying to create a line chart showing unresolved ticket count by date over time for my team's Jira project.  All I have been able to find via searching the forums is this 'Created VS Resolved' c...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi cearly65 ,

     

    Please try the measure.

     

    Open tickets = 
CALCULATE (
    DISTINCTCOUNT ( 'Jiradata'[Issue Key] ),
    FILTER (
        'Jiradata',
        'Jiradata'[Created].[Date] <= MAX ( 'Calendar'[Date] )
            && (
                'Jiradata'[Resolved].[Date] >= MAX ( 'Calendar'[Date] )
                    || Jiradata[Resolved] = BLANK ()
            )
    )
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi cearly65 - I was wondering if we could firstly simplify the complexity by turning the DateTime value to Date Only in Power Query as you import the data.

The challenge you face is setting the MAX ( Date ) then using this date to perform the Row Context validation against the Created Date, Resolved Date.  When using a Slicer or Date in the y-axis it will update the MAX ( date ) value to the current selection.  Something like the following should work - sorry edit to include BLANK resolved dates).

 

Count Open Issue = 
VAR _date = MIN ( Calendar[Date] , TODAY() )
VAR _filter = FILTER ( Issues , Issue[Create Date] <= _date && ( Issue[Resolved Date] >= _date || ISBLANK(Issue[Resolved Date] ) )
RETURN
      COUNTROWS ( _filter )

This measure would return current open issues when it is not filtered by Date, or when trying to look at future dates.  When you start filtering for past dates, it should calculate the Issue effective open as at the filtered date.  The line chart will show changes each day, so you may want to consider filtering to show the beginning of week to highlight week over week changes rather than daily.


 

 

cearly65's avatar
cearly65
Frequent Visitor
4 years ago

I did find that simplifying the date columns to date helped a lot with my attempts to make charts.  As for your proposed formula, I updated the references to what's in my file (see below), but I get the following error: "A single value for column 'Date' in table 'Calendar Table' cannot be determined.  This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result"

 

Count Open Issue =
VAR _date = MIN ( 'Calendar Table'[Date], TODAY())
VAR _filter = FILTER ( RSDjiradata , RSDjiradata[Created] >= _date && RSDjiradata[Resolved] <= _date )
RETURN
COUNTROWS ( _filter )