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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Noelia
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
Greg_Deckler
Super User
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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

 

Time in premises = 
var date_min_temp=
addcolumns( 
	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=
addcolumns( 
	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?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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. 

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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value. 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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