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.

cearly65's avatar
cearly65
Frequent Visitor
4 years ago

So I figured out how to make the chart after much trial and error (DAX is a very familiar yet frustratingly alien language compared to Excel).  I had created a second calendar table per the 'Created VS Resolved' chart thread with a column formula of Calendar = CALENDAR(MIN('Jiradata'[Created]),TODAY()).  I then added the below formula as a measure (sorry for the crap formatting)

 

Unresolved =
CALCULATE (
    COUNT ( 'Jiradata'[Issue Key] ),
    FILTER (
        ALLSELECTED ( 'Jiradata' ),
        'Jiradata'[Created] <= MAX ( 'Calendar'[Date] )
    )
)
    - (
        CALCULATE (
            COUNT ( 'Jiradata'[Issue Key] ),
            FILTER (
                ALLSELECTED ( 'Jiradata' ),
                'Jiradata'[Resolved] <= MAX ( 'Calendar'[Date] )
            )
        )
            - CALCULATE (
                COUNT ( 'Jiradata'[Issue Key] ),
                FILTER ( ALLSELECTED ( 'Jiradata' ), 'Jiradata'[Resolved] = BLANK () )
            )
    )
 
So I'm assuming that formula is atrocious and can be written much simpler, but I also noticed that if I put a date slicer on the chart and change the start date then the chart gets really weird.
 
Functional chart before adjusting start date via slicer

 

Chart after adjusting start date