Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi there,
I would like to create a new column within Power BI desktop in my matrix visualisation called "Load to Exit". It is calculated using "Event Date" of the "Exit" Event minus "Event Date" of the "Load" Event for each Vehicle ID.
Vehicle ID | Event | Event Date | Load To Exit (hh:mm) |
ABC123 | Load | 12/04/2017 16:36 | |
Exit | 12/04/2017 18:44 | 2:08 | |
XYZ789 | Load | 15/04/2017 10:27 | |
Exit | 16/04/2017 9:13 | 22:46 |
Vehicle ID comes from a data table called "VMS Vehicle"
Event comes from a data table called "VMS SupplyChainEvent"
Event Date comes from a data table called "VMS VehicleEvent"
(I have already specified the necessary relationships between the data tables)
Can someone kindly assist?
Thanks
Wes
Solved! Go to Solution.
Hi @wes-shen-poal,
Becuase I don't know your resource tables' structure, and the relationship between them. It's hard to give a specific formula for your requirement. Please try the following and check if it is successful.
Load to Exit=CALCULATE(MAX(Table[Event Date]),FILTER(ALLEXCEPT(Table,Table[Vehicle ID]),Table[Event]="Exit"))-CALCULATE(MAX(Table[Event Date]),FILTER(ALLEXCEPT(Table,Table[Vehicle ID]),Table[Event]="Load"))
If this doesn't resolved the issue, please post more details for further analysis.
Best Regards,
Angelia
Hi @wes-shen-poal,
Becuase I don't know your resource tables' structure, and the relationship between them. It's hard to give a specific formula for your requirement. Please try the following and check if it is successful.
Load to Exit=CALCULATE(MAX(Table[Event Date]),FILTER(ALLEXCEPT(Table,Table[Vehicle ID]),Table[Event]="Exit"))-CALCULATE(MAX(Table[Event Date]),FILTER(ALLEXCEPT(Table,Table[Vehicle ID]),Table[Event]="Load"))
If this doesn't resolved the issue, please post more details for further analysis.
Best Regards,
Angelia
Do you have a unique key that we can use to identify each event? For example if someone come on the 12th of april, one consideration could be to use a key that is the Vehicles ID plus the date. The only trick might be if the exit is on the following day, and making sure the key is correct.
Then i'd create another key column which is your event key concatinated with your "Entry/Exit" field, to create a value we can look up. Then i'd create the Load to Exit formula being something like
Load To Exit = 'DataTable'[Event Date] - LookupValue( 'DataTable'[Event Date], 'DataTable'[UniqueEventKey], 'DataTable'[UniqueKey] & "Entry" )
Thanks @Anonymous
I haven't seen this function before, so awesome I've now learned a new function.
I couldn't use it this time round since I can't use it in conjunction with a MAX() function as I may have multiple dates for a given event.
Thanks again.
Wes
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
103 | |
103 | |
87 | |
61 |
User | Count |
---|---|
162 | |
132 | |
131 | |
95 | |
86 |