I am new to the forum, new to Power BI and DAX, and decided to work on creating a ServiceNow dashboard for my service desk in power bi.
I have issues with the below and will be grateful for any assistance.
I am importing my data from SQL into power bi, and I need to create several measures to help build the dashboard.
The first which I am having a challenge with is: " % of tickets responded to in time". I am trying to add 45 mins to a timestamp column(opened time) and compare it to another column (first contact time) in the same table(incident), in order to determine if it was responded to in less than 45mins from arrival.
response time is calculated as below:
responded to in time = (opened time + 45 mins) < (first contact time)
The actual formula is:
% INC responded in time: [[Number of incidents responded to in time]]/[[Number of incidents that should have been responded to in time]]*100
any help on how to calculate responded to in time is most welcome.
Welcome to the community @seunao, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster.
Use the forum search to discover if your query has been asked before by another member.
When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
Hello seunao
Here are the number of steps I used to solve the issue.
Added 2 columns to concatenate ‘Opened Date’ and ‘Opened time’ as well as ‘First Contact Date’ and ‘First Contact Time’ as we need to consider both date and time.
Created new column called ‘responded to in time’ which store all true/false value based on 45 min time window.
@bharatsatyal thanks for this. This is great. I do have a question
I have been trying to work by the guiding principles not to create any additional columns except absolutely necessary, and to try to create a measure to address any requirements I have.
To my question, would you have a solution that can be arrived at by creating measures, without the additional columns?
I ask because my model is quite large(for me that is), I have about 20 tables, 6 fact and multiple dimension tables, and I would not want to slow it down by creating additional columns if they are not needed
% Of Responses Recorded In Time =
VAR _Total_No_Of_Responses =
CALCULATE( COUNTROWS( Data ) , ALL( Data ) )
VAR _No_of_Responses_In_Time =
IF(
DATEDIFF(
SELECTEDVALUE( Data[Opened Date] ) + SELECTEDVALUE( Data[Opened Time] ) ,
SELECTEDVALUE( Data[First Contact Date] ) + SELECTEDVALUE( Data[First Contact Time] ) ,
MINUTE ) < 45 ,
1 ,
0
)
VAR _Percentage_of_Responses_Recorded_In_Time =
DIVIDE( _No_of_Responses_In_Time , _Total_No_Of_Responses , 0 )
RETURN
_Percentage_of_Responses_Recorded_In_Time
And than to fix the grand total write another simple small measure. One of our expert @Greg has already created a post that address this specific issue. Below is the link of the post provided for the reference as well.
2). % Of Responses Recorded In Time - Totals
% Of Responses Recorded In Time - Totals =
SUMX(
SUMMARIZE(
Data ,
Data[Incident Number] ,
"@Totals" ,
[% Of Responses Recorded In Time] ) ,
[@Totals]
)
Below is the screenshot of the final results provided for the reference -
