locked
Create a measure to return latest value RRS feed

  • Question

  • Hi all, 

    I am trying to retrieve the latest job designation of each ID and transactions with blank ID.

    The current code that I am using for creating a new measure

    Result :=

    var i = SELECTEDVALUE('SampleData'[ID])

    var d = maxx(filter(all('SampleData'), 'SampleData' [ID]=i && 'SampleData' [Job Designation] <> BLANK()),'SampleData' [TS])

    var de = maxx(filter(all('SampleData'), 'SampleData' [ID]=i && 'SampleData' [aTS]=d), 'SampleData' [Job Designation])

    RETURN

    de

    Please refer to my sample data below. 

    TimeStamp

    Status

    ID

    Company ID

    Job Designation

    Current code

    Desired outcome

    24/9/2020 9:20:00PM

    Add

    123

    321

    Supervisor

    Manager

    Manager

    25/10/2020 10:30:00PM

    Update

    123

    321

    Manager

    Manager

    Manager

    26/11/2020 5:20:00PM

    Add

    456

    654

    Manager

    Manager

    Manager

    10/12/2020 9:20:00PM

    Update

    <Blank>

    654

    <Blank>

    (Row Deleted)

    (Return Blank)

    29/12/2020 9:20:00PM

    Update

    456

    654

    <Blank>

    Manager

    Manager

    24/9/2020 9:20:00PM

    Add

    789

    987

    Director

    CEO

    CEO

    25/10/2020 10:30:00PM

    Update

    789

    987

    <Blank>

    CEO

    CEO

    28/12/2020 9:20:00PM

    Delete

    789

    987

    CEO

    CEO

    CEO

    24/9/2020 9:20:00PM

    Add

    158

    852

    Manager

    Director

    Director

    25/10/2020 10:30:00PM

    Update

    158

    852

    Director

    Director

    Director

    26/11/2020 5:20:00PM

    Update

    <Blank>

    852

    <Blank>

    (Row Deleted)

    (Return Blank)

    28/12/2020 9:20:00PM

    Delete

    158

    852

    <Blank>

    Director

    Director

    As you refer to the sample data, I have "Update" transaction which involves updating of company details only. Therefore, user need not provide ID. The query that I currently have will remove the ones with blank ID.

    The desired outcome is to retrieve the latest job designation for each ID and return blank if ID is empty, but Company ID has data (i.e. 852 and 654). 

    Please help me out

    Thank you in advance.

    Sunday, September 27, 2020 1:48 PM

Answers