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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
EAfang
Frequent Visitor

Line Graph with 10 Minute Intervals on x-axis

Hello Power BI Users

Please I started working on a personal IT project that uses Power BI to create dashboards.

I want to create a line graph with 10 minutes intervals that shows a plot of KPI like ASR (Answer Seizure Ratio). The attached image below is a similar example.

powerbi-linechart.png

1 ACCEPTED SOLUTION

@EAfang

 

Create another calculated column

Setup Time2 =
DATE ( YEAR ( 'BI Folder'[Setup Time] ), MONTH ( 'BI Folder'[Setup Time] ), DAY ( 'BI Folder'[Setup Time] ) )
    + TIME ( HOUR ( 'BI Folder'[Setup Time] ), FLOOR ( MINUTE ( 'BI Folder'[Setup Time] ), 10 ), 0 )

Capture2.PNG

 

And an extra measure

ASR in last 10 mins = CALCULATE(DIVIDE (SUM ('BI Folder'[Answer Status]), COUNTA('BI Folder'[Session Status])),LASTNONBLANK('BI Folder'[Setup Time2],""))

 Capture.PNG

 

If it answers your question, please accept it as answer. For any question, feel free to let me know.

View solution in original post

6 REPLIES 6
ankitpatira
Community Champion
Community Champion

@EAfang so is your question related to getting 10 min interval on x-axis from the date time column that you will have ? if yes then you will first have to calculate difference between two time columns that you will have, change their data type to duration and then using Rounding function to make it 10 mins interval.

@ankitpatira 

Thanks for taking out time to help. Let me try my best to explain what I want to achieve with Power BI more clearly. The link below is a sample file I created from the actual dataset to quickly recreate the problem.

 

https://drive.google.com/file/d/0Bz0yHmC0QyGYYVNhZ1RickN0MVU/view?usp=drivesdk

 

There are three columns. 'Setup Time', 'Session Status' & 'Session Duration'
A 'Session Status' of 3 means that the call was answered.
A 'Session Status' of 2 means that the callee's phone rang but no pickup.
A 'Session Status' of 1 means that the call didn't get to the callee's phone or it was switched off.

The KPI I'm interested in at the moment is ASR.
ASR means Answer Seizure Ratio in percentage. So this is how I intend dealing with it;

1. I know that every ROW of data represents one session / call, so a row count should give me number of calls.
2. So I created a calculated column called 'Answer Status'
Answer Status = IF('BI Folder'[Session Status] = 3, 1, 0)
3. I then created a measure called ASR as follows
ASR = DIVIDE (SUM ('BI Folder'[Session Status]), COUNTA(BI Folder'[Session Status]))

I want to get the ASR at every 10 minutes interval (and it doesn't have to be on a line graph, I could use a card that updates with the latest 10 minute interval.
Instead Power BI will give me ASR by aggregations of Year, Month or Day. I want it in 10 minutes interval instead. Please how can I achieve this?

 

Power BI unaggregated to the minutesPower BI unaggregated to the minutes

@EAfang

 

Create another calculated column

Setup Time2 =
DATE ( YEAR ( 'BI Folder'[Setup Time] ), MONTH ( 'BI Folder'[Setup Time] ), DAY ( 'BI Folder'[Setup Time] ) )
    + TIME ( HOUR ( 'BI Folder'[Setup Time] ), FLOOR ( MINUTE ( 'BI Folder'[Setup Time] ), 10 ), 0 )

Capture2.PNG

 

And an extra measure

ASR in last 10 mins = CALCULATE(DIVIDE (SUM ('BI Folder'[Answer Status]), COUNTA('BI Folder'[Session Status])),LASTNONBLANK('BI Folder'[Setup Time2],""))

 Capture.PNG

 

If it answers your question, please accept it as answer. For any question, feel free to let me know.

@Eric_Zhang

 

Thank you! Thank you!! & Thank you!!!

In fact I'm loving Microsoft even more for a community like this.

 

You sure have answered my primary question and I have accepted your last post as the solution. But please is there a way to view the second to last ASR value (46.86%) on the CARD VISUAL instead of the last [ongoing] ASR (33.44%)?

 

Calculate LastnonblankCalculate Lastnonblank

 

 

Thanks

scass
Frequent Visitor

I'd add a complete interval flag on the database side, filter on that and get the MAX(Interval).

EAfang
Frequent Visitor

@scass

Thanks for sharing other alternative solutions.

 

@scass & @Eric_Zhang & @ankitpatira

 

I'll be signing up for Azure Services this week.

In addition to the above request (on how to view the second-to-last ASR on card visual using the CALCULATE function), I will also like to know if this implementation will work please.

 

IMPLEMENTATION

Want to analyze + visualize CDRs from several machines on a local network. So I intend spinning;

  1. A Virtual Machine on Azure running CentOS, which I'll VPN into our local network so I can transfer all CDRs to it.
  2. This virtual machine in turn will continuously send all CDRs to an Azure SQL Database that creates only relevant tables from CDRs.
  3. Power BI only then needs Query the SQL Database to get it's visuals as needed.
  4. Then I can simply get my workmates to sign up for the Power BI service so they could all view dashboards in REALTIME.

Please is there a more COST EFFECTIVE implementation that you can think of, maybe one that uses other Microsoft services?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.