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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
wes-shen-poal
Helper III
Helper III

Create new column calculated using two values of a given existing column for each ID

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 IDEventEvent DateLoad To Exit (hh:mm)
ABC123Load12/04/2017 16:36 
 Exit12/04/2017 18:442:08
XYZ789Load15/04/2017 10:27 
 Exit16/04/2017 9:1322: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

 

1 ACCEPTED SOLUTION
v-huizhn-msft
Microsoft Employee
Microsoft Employee

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


View solution in original post

4 REPLIES 4
v-huizhn-msft
Microsoft Employee
Microsoft Employee

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


Thanks @v-huizhn-msft

 

This has worked perfectly.

 

 

Anonymous
Not applicable

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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