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.
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
Solved! Go to Solution.
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])))
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.
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])))
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.
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])))
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 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |