cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TheKiltedGolfer
Helper III
Helper III

How to create four new columns that have most recent data point for each

In one data set/query, I have two DC names and from each I need to pull the most recent CPU % and Memory % from those (See attached).   I think it would be best to create 4 new columns (Latest %CPU RDC/GDC, Latest %Mem RDC,/GDC Latest %CPU West Campus, and Latest %Mem West Campus).  I have no idea if there is a DAX formula that will work or some other way that I can do this.  As you can see, each day has two rows of data for four data points that I need to extract but only from the most recent date.  I am sure this is something that is commonly done (pull specfic data from the most recent date) but I am not sure where to start.  If there is a better way to accomplish this then please let me know.  I will be trending the four data points over time in a line graph and have a dial guage that will display the most recent values.

 

Thank You

Help.png

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

So, here is an example that you can extrapolate upon to get the rest.

 

Most Recent # CPUs = CALCULATE(MAX(Table[# CPUs]),FILTER(Table,[date_time]=MAX(Table[date_time])))

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

9 REPLIES 9
dramus
Responsive Resident
Responsive Resident

Create a new Column for each.

Latest CPU %=

var LatestEntry=CALCULATE(LASTDATE('Table1'[date_time]),filter('Table1','Table1'[DC Name]=EARLIER([DC Name])))

return maxx(filter(Table1,[date_time]=LatestEntry &&'Table1'[DC Name]=EARLIER([DC Name])),[CPU %])

Thank you.  I was trying to us 'Lookupvalue' with not as much success as I was hoping.  However, when I am trying to use your solution I am getting a "Token Eof expected" error and when I select 'Show error' then 'LatestEntry' is highlighted.  Any suggestions?

I just copy and pasted the column definition back into PBI Desktop without an issue. 

Yeah... I am doing something stupid because it is not working.

Greg_Deckler
Super User
Super User

So, here is an example that you can extrapolate upon to get the rest.

 

Most Recent # CPUs = CALCULATE(MAX(Table[# CPUs]),FILTER(Table,[date_time]=MAX(Table[date_time])))

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

I think I discovered why it was not working for me.  I needed to enter your solution via New Column under Modeling and not DAX

Are you trying to enter those formulas in Query Editor? That will not work as that uses M code instead of DAX. You need to go to the Modeling tab and choose New Column and then paste in the code provided.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

I am trying to do something simple just to see if I can get anything to work.. I tried the following:

CALCULATE(MAX(RDC/WC_MEM_and_CPU[date_time]))

 

I do not get any syntax errors yet I to get Expression Error: The name 'CALCULATE' wasn't recognized.  Is there some sort of setting I need to correct?

I renamed my column from # CPUs to #CPUs but still got an error of invalid identifier with #CPUs highlited.  What I entered:

Custom = CALCULATE(MAX(RDC/WC_MEM_and_CPU[#CPUs]),FILTER(RDC/WC_MEM_and_CPU[date_time]=MAX(RDC/WC_MEM_and_CPU[date_time])))

 

I also tried this with the comma after Filer Table per your note:

CALCULATE(MAX(RDC/WC_MEM_and_CPU[#CPUs]),FILTER(RDC/WC_MEM_and_CPU,[date_time]=MAX(RDC/WC_MEM_and_CPU[date_time])))

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.