Create a measure that filters a status column by the latest date

[Mattr_TEM]

Hello,

I am a newbie to powerbi and especially DAX.  I have a database that we use to keep track of the inventory of our rentable gear. I am trying to create a measure that will give the most recent status of the unit; Rented, Ready to Rent, Check Unit, Do Not Rent, Not Tracked, Unknown.

I have a look up table - EquipmentStatus.

And a data table - EquipmentList:

I would like to create a measure that gives me the latest(CreateTS) status(EquipmentStatusID) of each unique unit(TagNumber). 

Any and all help would be greatly appreciated!

[Anonymous]

Hi @Mattr_TEM,

You can try to use below formula if it suitable for your requirement.

Measures:

LastTs=
var currentTag=LASTNONBLANK(Table[TagName],[TagName])
return
MAXX(FILTER(ALL(Table), Table[TagName]=currentTag),[CreateTS])

LastStatus=
var currentTag=LASTNONBLANK(Table[TagName],[TagName])
return
LOOKUPVALUE(Table[EquipmentStatusID],Table[TagName],currentTag,Table[CreateTS],[LastTs])

Regards,
Xiaoxin sheng

[Mattr_TEM]

Thank you so much for replying. Like I said in my post I am very new to DAX.  I have a few questions about the code you posted:

//This is creating a measure for the last time stamp named LastTS
LastTs=
//This creates a varchar currentTag field based on the the last non blank row on the Table[TagName], Column [TagName]
var currentTag=LASTNONBLANK(Table[TagName],[TagName])
//This returns the value of the currentTag based on the MAX CreateTS.
return
MAXX(FILTER(ALL(Table), Table[TagName]=currentTag),[CreateTS])

//This creates a measure for the last status named LastStatus
LastStatus=
//This created another varchar field currentTag based on the last non blank row on the Table[TagName], Column [TagName]
var currentTag=LASTNONBLANK(Table[TagName],[TagName])
//This returns the value of the current status of currentTag based on the Table[EquipmentStatusID], Table[TagName], currentTag, Table[CreateTS], using the measure [LastTs]
return
LOOKUPVALUE(Table[EquipmentStatusID],Table[TagName],currentTag,Table[CreateTS],[LastTs]) 

So if I am right on what is going on here I see an issue.  

1. All the info is in two tables only, EquipmentStatus and Equipment List.

Thanks again for all the help!!

[Anonymous]

Hi @Mattr_TEM,

Some comment of measures:

1. LastTs

First, I use LASTNONBLANK function to get the current row content. Then, I use this variable to filter the records which has the same TagName. Finally, I use Maxx funciton to get the biggest date from the filtered table.

2. LastStatus

Use the result CreateTs and the current TagName as the parameter to find out the specify records.

If you only want to get the last state id, you can try to use below formula:

LastStatusID=
var currentTag=LASTNONBLANK(Table[TagName],[TagName])
return
LOOKUPVALUE(Table[EquipmentStatusID],Table[TagName],currentTag,Table[CreateTS],MAXX(FILTER(ALL(Table), Table[TagName]=currentTag),[CreateTS]))

Regards,
Xiaoxin Sheng

[Phil_Seamark]

This might not what you are after but try creating this measure

Latest Create TS = CALCULATE(MAX('EquipmentList:'[CreateTS])) 

And then creating a Grid visual with just your TagNumber (from EquipmentList and this measure.