Create a measure to return latest value

Question

I am trying to retrieve the latest job designation of each ID and transaction with blank field. 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.

enter image description here

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.

Answer 1

If I understand you correct, you need this below measure for your purpose-

latest_designation = 

MAXX(
    TOPN(
        1,
        FILTER(
            ALLSELECTED('SampleData'),
            'SampleData' [Job Designation] <> BLANK()
        ),        
        'SampleData' [TS],
        DESC
    ),
    'SampleData' [Job Designation]
)    

As you have slicer for selecting ID, you do not need filter table using Slicer Value insider the measure.

Answer 2

I have found the answer. I didn’t select “Show items with no data”.