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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wes-shen-poal
Helper III
Helper III

Calculated Columns

Hi there,

 

 

I am a little stuck in creating Calculated Columns which are column H and column I in screenshot below for any given VehicleID (Column A).

 

Capture.PNG

 

I currently have Columns A:G in my 'VMS VehicleEvent' data table.

 

Can someone kindly provide me the DAX formula I need to calculate:

 

1) Column H, which takes the Event Name (column E) of the Latest Vehicle Event ID (column G)

2) Column I, which take the LocalEventDate (column F) of the Latest Vehicle Event ID (column G)

 

 

Once I have column I, I can then calculate a measure Active Dwell Time (column J) which is column I minus column C.

 

Thanks in advance for your help

Wes

1 ACCEPTED SOLUTION

Hi @wes-shen-poal

 

You could try these two formulas as calculated columns. (The VehicleEventID should be unique)

 

ColumnH =
CALCULATE (
    VALUES ( Table1[Event Name] ),
    FILTER (
        ALL ( Table1 ),
        'Table1'[VehicleEventID] = EARLIER ( 'Table1'[Latest Vehicle Event ID] )
    )
)
ColumnI =
CALCULATE (
    VALUES ( Table1[LocalEventDate] ),
    FILTER (
        ALL ( Table1 ),
        Table1[VehicleEventID] = EARLIER ( Table1[Latest Vehicle Event ID] )
    )
)

Calculated Columns.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

 

 

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
vanessafvg
Super User
Super User

@wes-shen-poal if you calculating it at a column level then you should just be able to do a date diff?  maybe i am not understanding why you need to find column i, are they not in the same row?

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Hi @vanessafvg

 

Thanks for looking into this for me.

 

I need to find column I because once I can isolate the Latest Event Date from the other Event Dates within a column (i.e. column I) for each Vehicle ID, I will calculate the Dwell Time (which = Latest Event Date minus Vessel Depart Date) for each Vehicle ID. And then I can calculate the average Dwell Time of all Vehicle IDs.

 

Hope that makes sense.

 

Thanks,
Wes

Hi @wes-shen-poal

 

You could try these two formulas as calculated columns. (The VehicleEventID should be unique)

 

ColumnH =
CALCULATE (
    VALUES ( Table1[Event Name] ),
    FILTER (
        ALL ( Table1 ),
        'Table1'[VehicleEventID] = EARLIER ( 'Table1'[Latest Vehicle Event ID] )
    )
)
ColumnI =
CALCULATE (
    VALUES ( Table1[LocalEventDate] ),
    FILTER (
        ALL ( Table1 ),
        Table1[VehicleEventID] = EARLIER ( Table1[Latest Vehicle Event ID] )
    )
)

Calculated Columns.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

 

 

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you @v-jiascu-msft this is perfect!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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