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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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.


@ 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!:
Mastering Power BI 2nd Edition

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?


@ 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!:
Mastering Power BI 2nd Edition

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.


@ 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!:
Mastering Power BI 2nd Edition

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors