Forum Discussion
Filter a Unique ID by most recent date
I have a PowerBI Template for the Microsoft Windows Update for Business Reporting data.
The table shows each (daily) upload of the individual devices Update Status which includes the date. This may mean within a week period there are 7 rows for each day, but I would only want the latest update.
Is it possible to filter the data so for each "DeviceID" only the latest "time Generated" is shown?
hope that makes sense. Thank you all
6 Replies
- amustafa
Solution Sage
You can create a summarized table. See DAX below and adjust the table name.
SummarizedTable =SUMMARIZECOLUMNS('Table1'[Device ID],"LatestTime", MAX('Table1'[Time generated]),"Device Name", FIRSTNONBLANK('Table1'[Device Name], 0))If I answered your question, please mark this thread as accepted and Thums Up!
Follow me on LinkedIn:
https://www.linkedin.com/in/mustafa-ali-70133451/ Thank you very much for the information, it has got me closer, until I got to the point now where the "latest time" shows for each Device, however because there are other values for the other fields then both still show.
What i really need is the "Largest" number for the OS Build folder, if there are 2 showing.. like the image below. Sorry for additional queries.The visual table contains data from two tables that have a relationship - one of these is the DAX query created from the information provided earlier. UCClient is the main data source from Azure Log Analytics
UpdateStatusPerDevice =SUMMARIZECOLUMNS('UCClient'[DeviceName],"Date Uploaded", MAX('UCClient'[TimeGenerated]),"Device ID", FIRSTNONBLANK('UCClient'[AzureADDeviceId], 0))from the table of UCClient I am trying to get the following
Most Recent Date from "TimeGenerated" AND Highest Value from "OSBuild"Hopefully something could be possible
Thank you
- amustafa
OK, try this...
UpdateStatusPerDevice =
SUMMARIZECOLUMNS(
'UCClient'[DeviceName],
"Date Uploaded", MAX('UCClient'[TimeGenerated]),
"Device ID", FIRSTNONBLANK('UCClient'[AzureADDeviceId], 0),
"Max OS Build", MAX('UCClient'[OS Build])
)Really appreciate the speedy response. Unfortunately the same remained, the multiple values for the "OSBuild" property still showed
- amustafa
In my test, it worked.
SummarizedTable =SUMMARIZECOLUMNS('Table1'[Device ID],"LatestTime", MAX('Table1'[Time generated]),"Device Name", FIRSTNONBLANK('Table1'[Device Name], 0),"Highest OS Build", MAX('Table1'[OS Build]))
