cancel
Showing results for
Did you mean: Frequent Visitor

## how to turn a column containing an EARLIER into a measure?

I want to calculate the amount of time it takes to fix cars. When on the premises one car can undergo many different processes with start and end date/times that will overlap with each other. It is necessary to use EDIFF on the earliest and latest dates for every car, so we need to find the processes that overlap and select the right dates/times. This was my attempt to do so using columns:

`Date_min = CALCULATE(MIN([Date_Entry]) ; FILTER(Operations ; [Date_Entry] < EARLIER([Date_entry]) && [Car] = EARLIER([Car]) && Operations[Date_Exit]=EARLIER(Operations[Date_Exit])))`

`Date_max = CALCULATE(MAX([Date_Closed]) ; FILTER(Operations ; [Date_Closed] < EARLIER([Date_Closed]) && [Car] = EARLIER([Car]) && Operations[Date_Exit]=EARLIER(Operations[Date_Exit])))`

These columns will give the earliest date/time a process was started on a car and the latest date/time a process was closed before the car left the premises (Date_Exit). If Date_Min is the same as Date_Entry then it will return a blank (same with Date_max and Date_Closed). By creating a new column with the correct start and end dates I can calculate for how long the car was in the premises undergoing a process (duplicates will be deleted). However, there is too much data and Power Bi freezes if I use columns in order to obtain the duration of processes without overlaps. I am trying to do something similar to this with measures but EARLIER cannot be used. If anyone has any advice on how to optimize the columns or how to turn them into measures I would really appreciate it.

5 REPLIES 5  Super User

So, the answer to how to do this is in a measure is to use a VAR statement to create a temp table that uses the ADDCOLUMNS to add the column that uses EARLIER.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition  DAX is easy, CALCULATE makes DAX hard...  Super User

That's the general idea. The part in red doesn't make sense to me:

```Time in premises =
var date_min_temp=
operations;
"Date_min";
calculate(
min( operations( date_entry); 		filter(
operations;
operations(date_entry<=earlier(operations(date_entry)
&&
operations(car)=earlier(operations(car))
&&
operations(date_exit)=earlier(operations(date_exit))
)
)
)

var date_max_temp=addcolumns( operations; "Date_max"; calculate( max( operations( date_closed); filter( operations; operations(date_closed>=earlier(operations(date_closed) && operations(car)=earlier(operations(car)) && operations(date_exit)=earlier(operations(date_exit)))))
return date_max_temp-date_min_temp```

I would think that it should be something like:

```Time in premises =
var date_min_temp=
operations;
"Date_min";
calculate(
min( operations( date_entry));
filter(
operations;
operations(date_entry)<=earlier(operations(date_entry)) 			&&
operations(car)=earlier(operations(car))
&&
operations(date_exit)=earlier(operations(date_exit))
)
)
)

var date_max_temp=addcolumns( operations; "Date_max"; calculate( max( operations( date_closed); filter( operations; operations(date_closed>=earlier(operations(date_closed) && operations(car)=earlier(operations(car)) && operations(date_exit)=earlier(operations(date_exit)))))
return date_max_temp-date_min_temp```

It's weird reading your syntax because I use commas instead of semi-colons and brackets [ ] instead of parens (). I figure that's just a regional thing?

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition  DAX is easy, CALCULATE makes DAX hard... Frequent Visitor

I keep getting the same error. I am using multiple columns and it cannot be converted to a scalar value.

Sorry about the syntax, I didn't write it properly. My version of Power BI uses semicolons instead of colons but we still use square brackets for columns.  Super User

Can you post the full text of the error that you are getting? Might help me to pinpoint where in the formula there is something going wrong.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition  DAX is easy, CALCULATE makes DAX hard... Frequent Visitor

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value. Announcements #### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day! #### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference. Top Solution Authors
Top Kudoed Authors
Users online (1,821)