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
Alex_0201
Post Partisan
Post Partisan

editing columns for a line chart

Hi everyone, help is needed 😞

I need to create a line chart that shows time vs depth for a digging/drilling process.

All I have now is follwing columns:

- step No: 1,2,3,...

- time in hrs: time it takes to drill the hole from one depth to another

- depth from: starting depth

- depth to: end depth

- activity: type of activity, where drilling is the main activity and thes rest is secondary (like replacing tools, waiting on weather).

   Capture.PNG

 

Left is my current table. Right is how it should look like, with the line chart.        

Cells marked by yellow indicate secondary, non-drilling activities (replacing and waiting on weather) and should be shown on the line chart as flat lines since the depth doesn't change.

The actual table contains many more rows, so not possible to edit it manually.

Should I start with two measures?

Thanks in advance!

 

 

7 REPLIES 7
AllisonKennedy
Super User
Super User

I would try creating a Cumulative total based on Step No and plot Depth To vs Running Total Time (doesn't look like you're using/need Depth From, but if you are please explain how to handle the inconsistencies in steps 6 and 7)
See this post for reference and refer to Customer Class example: https://www.sqlbi.com/articles/computing-running-totals-in-dax/

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

@AllisonKennedy  Hi Alison, thanks for your promt comment. I've added some annotations to the picture.

@Alex_0201  Thanks, that's exactly what I thought. You'll need to add that first row in there as a data point, but otherwise focus on the blue and red boxes in your left picture.

AS CALCULATED COLUMN:

TotalTime = CALCULATE(SUM(Table[Time]), Table[Step]<=EARLIER(Table[Step]))
 
Then graph that with [Depth to, m] column.
 
For the green boxes, it seems like this data point is cheating in a way, as looking at the left table there's no assurance that the activity was actually drilling or that it started at 0? Where does this information come from? With Power BI it all needs to systematically connect and talk to each other, being pulled from somewhere.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

@AllisonKennedy the formula

TotalTime = CALCULATE(SUM(Table[Time]), Table[Step]<=EARLIER(Table[Step])) returns the same values in the new column. What am I doing wrong here?

Sorry, I forgot to replace the ALLEXCEPT I was using with something relevant to your data. Do you have any other columns in the table? You will need an ALL() or ALLEXCEPT() as well;
TotalTime = CALCULATE(SUM(Table[Time]), ALL(), Table[Step]<=EARLIER(Table[Step]))

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

@AllisonKennedy No, that's it. What's with ALL and ALLEXCEPT? 🙂

@AllisonKennedy Nice!

1. so I start with creating a new measure or rather a new column?

2. Regarding the first line in the new column the green boxes), it might as well start with step No 1. What's important is that the time starts at 0 and the depth is copied from the first line in the original tabel (step No 1), or picked as the shallowest depth (30 m). 

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.