Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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.
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.
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?
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.
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
85 | |
84 | |
67 | |
49 |
User | Count |
---|---|
131 | |
110 | |
97 | |
71 | |
67 |