- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Calculated Column to Measure

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

justclicknbtns

Frequent Visitor

Calculated Column to Measure

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-05-2018
07:32 AM

I'm attempting to make the following calculated column, but it requires too much memory. When I try to make it a measure the EARLIER function does not work.

Operating Days test = CALCULATE(count(DepthVsDays[HoleDepth])/288, FIlter(ALLEXCEPT(DepthVsDays,DepthVsDays[WellName]),DepthVsDays[EdrDateTime]<=EARLIER(depthvsdays[edrdatetime])))

ime])))

My data table follows this format, but has 6M ish rows. My final goal is to have a scatter plot with a slicer that can filter for different hole depths and it will shift the operating days to the left by not counting operating days that have been filtered out by the slicer. I think I can accomplish this as long as the hole depth column is worked into the operating days formula, but I'm not positive how. The EdrDateTime column is always in 5 minute increments for a particular well.

Any help would be greatly appreciated!

Thanks!

4 REPLIES 4

Stachu

Super User

Re: Calculated Column to Measure

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-05-2018
07:39 AM

can you explain again what do you want to show?

e.g. for well B, what would be the value for Operating Days?

justclicknbtns

Frequent Visitor

Re: Calculated Column to Measure

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-05-2018
07:44 AM

The data is all in 5 minute increments, so the first part of the equation is just counting the rows on that particular well and counting each one as 5 minutes, then dividing by 288 to get the units into days. So on well b at hole depth 2034.6 I would have 5 minutes accumulated, then 10 minutes at 2037.8, then 15 minutes at 2040.9, and 20 minutes at 2043.6.

Stachu

Super User

Re: Calculated Column to Measure

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-05-2018
08:12 AM

I think you actually need a calculated column, something like this:

Operating Days = VAR _Well = DepthVsDays[Well] VAR _First = CALCULATE(MIN(DepthVsDays[ErdDateTime]),FILTER(DepthVsDays, DepthVsDays[Well] = _Well)) RETURN (DepthVsDays[ErdDateTime]-_First)/288

and then put

in Details - Hole Depth

in Legend - Well

in X axis - Column

in Y Axis - Hole Depth

justclicknbtns

Frequent Visitor

Re: Calculated Column to Measure

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-05-2018
09:38 AM

First off thanks for the response, I initially thought a calculated column wasn't an option since it wouldn't run with my initial code, but your recommendations are running very quickly, so that opens up other options.

Operating Days test = Var _Well = DepthVsDays[WellName] VAR _First = CALCULATE(min(DepthVsDays[EdrDateTime]), FIlter(ALL(DepthVsDays),DepthVsDays[WellName] = _Well)) Return (DepthVsDays[EdrDateTime]-_First)

I tried running it with the above since you're using a time subtraction the /288 wasn't necessary. Some of the wells have gaps in their operating days though, so a well will accumulate operating days for 5 or 6 days then break for 2 weeks and start accumulating days again in the data set. Since the equation is subtracting from the smallest time it's leaving gaps. It's also not adjusting the operating days to zero from the beginning of the slicer.

Here's the updated picture. Since the slicer is currently set at 771.04, I'm hoping to have each of the operating days start at 0 from that hole depth.