Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

9 REPLIES 9
dramus
Continued Contributor
Continued Contributor

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.