Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bimystic
Helper I
Helper I

Showing Incidents Created and Resolved in the same graph or line graph

I have the below data for incidents

SNIncident Number DescStateCreated_DateResolved_Date
1INC1Unable to loginResolved10-May-2011-May-20
2INC2Error on VMNew15-May-20 
3INC3Account lockedResolved16-May-2018-May-20
4INC4Unable to loginResolved17-May-202-Jun-20
5INC5Account lockedIn Progress28-May-20 
6INC6Error on VMResolved1-Jun-201-Jun-20
7INC7Unable to loginIn Progress2-Jun-20 
8INC8Account lockedOn Hold2-Jun-20 
9INC9Error on VMOn Hold5-Jun-20 
10INC10Account lockedResolved6-Jun-208-Jun-20

 

I need to create a report where I can show the number of incidents created and resolved in the same graph.

 

When I am using the Created_Date in Y axis and Count(Incident Number ) and Count(Resolved_Date) in values the value are not correct. The Resolved shows values in month even when there is no Resolved. The reason is that it picks Created data i.e. it counts incidents that were created in Jan and resolved any month later.

 

How can I show incidents resolved and created on month basis.

 

 

 

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

Hey @bimystic ,

 

create a dedicated calendar table, create two relationships from the date column of the calendar table (on the one side) and the Created_Date and the Resolved_Date column.

You have to be aware that only one relationship can be marked as active.

 

Use the date column from the calendar table on the axis of your visual.

 

Create at least one measure like so:

CALCULATE(
COUNT('tablename'[Incident Number])
, USERELATIONSHIP('CalendarTable'[DateColumn] , 'tablename'[inactive date column]
)

 

Hopefully, this is what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

3 REPLIES 3
Icey
Community Support
Community Support

Hi @bimystic ,

 

Just as @TomMartens said, you need to create a Date dimension table. You can try this:

Dates =
CALENDAR (
    MIN ( MIN ( 'Table'[Created_Date] ), MIN ( 'Table'[Resolved_Date] ) ),
    MAX ( MAX ( 'Table'[Created_Date] ), MAX ( 'Table'[Resolved_Date] ) )
)

 

Then, create relationships.

re.jpg

 

Then, create measures.

Created count = COUNT ( 'Table'[Incident Number ] )
Resolved count = 
CALCULATE (
    COUNT ( 'Table'[Incident Number ] ),
    USERELATIONSHIP ( 'Dates'[Date], 'Table'[Resolved_Date] ),
    'Table'[Resolved_Date] <> BLANK ()
)

resolve.PNG

 

BTW, .pbix file attached.

 

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

TomMartens
Super User
Super User

Hey @bimystic ,

 

create a dedicated calendar table, create two relationships from the date column of the calendar table (on the one side) and the Created_Date and the Resolved_Date column.

You have to be aware that only one relationship can be marked as active.

 

Use the date column from the calendar table on the axis of your visual.

 

Create at least one measure like so:

CALCULATE(
COUNT('tablename'[Incident Number])
, USERELATIONSHIP('CalendarTable'[DateColumn] , 'tablename'[inactive date column]
)

 

Hopefully, this is what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

I tried this method but Stilll I am not able to bring date wise distribution. Could you please help me?

Ashutosh_domle_0-1674661539951.png

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.