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.
I'm trying to create measures to:
Based on this https://community.powerbi.com/t5/Desktop/Find-value-for-first-and-last-date-by-person/td-p/61984 help I have tried to update those to filter to only the values that are not blank and not zero.
The problem is that Last and first are both returning the same values.
The measures I'm using:
FirstDiskUsed (sums over server names) =
SUMX (
VALUES( '123Combined_vw_DPM_Disk_Usage_Replica'[ProductionServerName] ),
CALCULATE ( MIN ( '123Combined_vw_DPM_Disk_Usage_Replica'[DiskUsed] ), FILTER( '123Combined_vw_DPM_Disk_Usage_Replica', '123Combined_vw_DPM_Disk_Usage_Replica'[DiskUsed] > 0 && '123Combined_vw_DPM_Disk_Usage_Replica'[DiskUsed] <> BLANK() && FIRSTDATE( '123Combined_vw_DPM_Disk_Usage_Replica'[StartDateTime] ) )
))
LastDiskUsed (sums over server names) =
SUMX (
VALUES( '123Combined_vw_DPM_Disk_Usage_Replica'[ProductionServerName] ),
CALCULATE ( MIN ( '123Combined_vw_DPM_Disk_Usage_Replica'[DiskUsed] ), FILTER( '123Combined_vw_DPM_Disk_Usage_Replica', '123Combined_vw_DPM_Disk_Usage_Replica'[DiskUsed] > 0 && '123Combined_vw_DPM_Disk_Usage_Replica'[DiskUsed] <> BLANK() && LASTDATE ( '123Combined_vw_DPM_Disk_Usage_Replica'[StartDateTime] )) )
)
Sample data:
ProductionServerName,DiskUsed,StartDateTime
server1 | 112525312 | 1/1/2016 8:00 |
server1 | 168787968 | 7/1/2018 7:00 |
server1 | 56262656 | 7/8/2018 7:00 |
server1 | 56262656 | 7/15/2018 7:00 |
server2 | 9.91383E+12 | 1/1/2014 8:00 |
server2 | 30331232256 | 1/1/2014 8:00 |
server2 | 47233200128 | 1/1/2014 8:00 |
server2 | 2.78859E+12 | 3/23/2014 7:00 |
server2 | 1.61244E+11 | 7/1/2018 7:00 |
server2 | 8.48197E+12 | 7/8/2018 7:00 |
server2 | 53904424960 | 7/8/2018 7:00 |
server2 | 8.48198E+12 | 7/15/2018 7:00 |
server2 | 53669543936 | 7/15/2018 7:00 |
server3 | 1/1/2014 8:00 | |
server3 | 30036901888 | 1/1/2014 8:00 |
server3 | 46338138112 | 1/1/2014 8:00 |
server3 | 3.90742E+12 | 3/23/2014 7:00 |
server3 | 19699400704 | 3/23/2014 7:00 |
server3 | 3.93733E+12 | 3/30/2014 7:00 |
server3 | 47206088704 | 7/8/2018 7:00 |
server3 | 5.93578E+12 | 7/15/2018 7:00 |
server3 | 52610113536 | 7/15/2018 7:00 |
server3 | 47204257792 | 7/15/2018 7:00 |
DiskUsedChange (sums over Server Names) =
[LastDiskUsed (sums over server names)] - [FirstDiskUsed (sums over server names)]
Solved! Go to Solution.
That's because you need to use MAX instead of MIN in your LastDiskUsed measure.
That's because you need to use MAX instead of MIN in your LastDiskUsed measure.
So, I don't want the largest and smallest numbers, I want the number associated with the oldest date and newest date... does that make sense? Meaning I need to see what the latest number is, and it won't always be the greatest number. Are these queries doing that?
That's a good question, I'll have to check on that.
Thanks! Look forward to hearing back.
Thank you, so glad to have that sorted out after trying for so long and not seeing that simple thing!
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 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |