Forum Discussion
Comparing Date from Var
Hello,
I'm trying to return the current battery percentage for each LeakDetector. A device checks in multiple times per day with its battery remaining percentage and is logged in a Checkins table. I'm using direct query on the source data. I tried the below code and its returning blank results. If I replace lastCheckin in the filter with a hard coded dateTime value that I know is viable, then it returns the value I want. I dont understand why the var cant be used to determine the date. Does anyone see an apparant issue?
*Note: I used max to determine var detector because I needed a single value, and I'm viewing this measure in a table next to the LeakDetectorId's, so that leaves me with a single value to choose from. This seems to work fine.
CurrentBatteryLevel = var lastCheckin = max(Checkins[OccurredAt]) var detector = max(Checkins[LeakDetectorId]) return CALCULATE(Min(Checkins[BatteryLevel]),filter(Checkins, Checkins[LeakDetectorId] = detector && Checkins[OccurredAt] = lastCheckin))
HI jshankle89,
You can try to use below formula if it works on your side.
CurrentBatteryLevel = VAR lastCheckin = MAX ( Checkins[OccurredAt] ) VAR detector = MAX ( Checkins[LeakDetectorId] ) RETURN MINX ( FILTER ( ALL ( Checkins ), [LeakDetectorId] = detector && [OccurredAt] = lastCheckin ), [BatteryLevel] )If above not help, please share some sample data for further test.
Regards,
Xiaoxin Sheng
2 Replies
HI jshankle89,
You can try to use below formula if it works on your side.
CurrentBatteryLevel = VAR lastCheckin = MAX ( Checkins[OccurredAt] ) VAR detector = MAX ( Checkins[LeakDetectorId] ) RETURN MINX ( FILTER ( ALL ( Checkins ), [LeakDetectorId] = detector && [OccurredAt] = lastCheckin ), [BatteryLevel] )If above not help, please share some sample data for further test.
Regards,
Xiaoxin Sheng
I'm going to do some deeper testing, but this seems to do exactly what I needed. Thanks so much! I will look into the documentation to learn the difference between the Min and MinX functions, in addition to the use of the All function. This was one of the first Power BI reports I have created, so its been a great learning experience.
