Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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
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!!
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |