Power BI Challenge 8 - Jira IT Service Desk Analysis Entry from Nebiyu

[EnterpriseDNA]

Here’s Nebiyu’s entry for Power BI Challenge 8. @Neba, feel free to add other details of your work.

Here is the link to the report:

Power BI Report

And here’s how Nebiyu described it:

Hello EveryOne,

Here is my submission for challenge 8 which I had a lot of fun working on.

Data Model : -

For this challenge I created a Data model which consists of 6 Dimension tables and one Fact table in Power Query. ( I guess I am getting comfortable using Power Query to create a Data
model instead of SQL Server as I did before ).

Dimension Tables : -

The five dimensions were created using distinct values of :-

1. Projects
2. Issue Types
3. Statuses
4. Assignee
5. Watchers ( I Unpivoted Watcher_4 , Watcher_5 , Watcher_6 and Watcher_7 and merged them with Watcher and removed duplicates ).and
6. Dates ( Thanks @Melissa ) .

Fact Table:-

1. Jira_Tickets

DAX Calculations:-

Once the Model was created, most of the calculations were not too difficult. But, some of the data needed to be modified in order to make it work.

To verify the validity of the Tickets, based on @haroonali1000 's instruction, I used the following Measure:-

Verifying Tickets = IF (

					ISBLANK (
					MAXX ( Jira_Tickets, Jira_Tickets[Due Date] ) ),
					       "Incomplete Ticket",
				        IF (      																 
                                                 MAXX ( Jira_Tickets,
        							DATEDIFF ( Jira_Tickets[Created], Jira_Tickets[Due Date], DAY ) 
                                                              ) < 0,
                                                              "Implementation Issues",
                                                   "in "  & MAXX ( Jira_Tickets,
                                                                  DATEDIFF ( Jira_Tickets[Created], Jira_Tickets[Due Date], DAY )
                                                                                      ) & " Days"

)

)

Then I created a Calculation Group in Tabular Editor to classify each ticket based on the above measure.

E.g. For incomplete tickets, the following DAX calculation was used in Tabular Editor.

CALCULATE (

SELECTEDMEASURE (),

FILTER (

    Jira_Tickets,

    [Verifying Tickets] = "Incomplete Ticket"

			)

			)

and the same for Tickets with Implementation Issues and the rest of the Tickets were assumed as Valid Tickets.

I also used Calculation Groups for to merge Statuses: -

E.g. the following Statuses were assumed as Resolved.

CALCULATE (

SELECTEDMEASURE (),

Jira_Tickets[Status] = "Completed"

    || Jira_Tickets[Status] = "Closed"

    || Jira_Tickets[Status] = "Done"

    || Jira_Tickets[Status] = "Resolved"

)

This is the first time I used Calculation Groups and I found them to be very useful ( especially for multiple Time Related DAX Calculations ).

Visualizations :-

For the theme of this Report I used one of Atlassian’s Logo and matching blue colors for most of the Report.

I took a visualization Idea of combining a Column chart and a Matrix to display bars for the months from one of the Top Experts on this forum (Thanks @MudassirAli ).

To learn about the real-life scenario presented for the challenge, be sure to click on the image below.

[EnterpriseDNA]

This post is part of the Enterprise DNA platform improvements, through the Data Challenge our members can now share how they built their dashboard and what their inspiration is in building it. We hope all members can utilize it efficiently.

[sam.mckay]

Love the clean design that you’ve implemented here and especially whilst incorporating some custom visuals. Sometimes custom visuals can be hard to work into Power BI reports with lots of other visuals I’ve found because the color variability isn’t as extensive. But I think you have combined all different visualization techniques here super effectively.

This particular report would not look at a place as a web based application sitting on top of Jira.

I actually really also like the shadowed grid design you’ve used. It makes the visuals pop out from the background. It’s actually something that I think is easy on the eye and easy to navigate around as a consumer.

Overall really impressed by this submission actually. I can tell you have put a lot of work into it and you have had to think about what a consumer would want to know and in what order they would want to view it. It’s all about storytelling. I think in you’ve done a good job of sticking to the brief and walking a consumer affectively through the information.

Sam