Forum Discussion

wes-shen-poal's avatar
wes-shen-poal
Icon for Helper III rankHelper III
9 years ago
Solved

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

 

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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


4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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"
)

     

     

     

    • wes-shen-poal's avatar
      wes-shen-poal
      Icon for Helper III rankHelper III
      9 years ago

      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

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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