cancel
Showing results 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

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!:
The Definitive Guide to Power Query (M)

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!:
The Definitive Guide to Power Query (M)

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!:
The Definitive Guide to Power Query (M)

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

#### 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 Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Find out what's new and trending in the Fabric Community.

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors