Forum Discussion
Helper IIICount by Catagory
Hi,
I trying to get number of DeviceID for a each catagory (binLegendColumn).
But it has to be for latest value for a given timestamp.
As you can see below, i am able to fetch latest hours value (<= timestamp)
From this, expected output
| binLegend | number of distinct deviceid |
| < 0 Hours | 1 |
| 0 - 24 Hours | 2 |
| 25 - 100 Hours | 1 |
| > 100 Hours | 1 |
my pbix file:
https://1drv.ms/u/s!AhI1WOiXwAe-qNl6FDPyeZvIv-Xzew?e=98X6Fa
Here is my sample data.
| DeviceID | lastValueHours | binLegendColumn | timestamp |
| A | -345 | < 0 Hours | 7/13/2020 0:00 |
| A | -150 | < 0 Hours | 7/12/2020 0:00 |
| A | 0 | 0 - 24 Hours | 7/11/2020 0:00 |
| A | 20 | 0 - 24 Hours | 7/10/2020 0:00 |
| A | 50 | 25 - 100 Hours | 7/9/2020 0:00 |
| A | 200 | > 100 Hours | 7/8/2020 0:00 |
| B | 100 | 25 - 100 Hours | 7/12/2020 0:00 |
| B | 70 | 25 - 100 Hours | 7/11/2020 0:00 |
| B | 24 | 0 - 24 Hours | 7/10/2020 0:00 |
| B | 0 | 0 - 24 Hours | 7/9/2020 0:00 |
| B | -300 | < 0 Hours | 7/8/2020 0:00 |
| C | 23 | 0 - 24 Hours | 7/13/2020 0:00 |
| C | 0 | 0 - 24 Hours | 7/12/2020 0:00 |
| C | 0 | 0 - 24 Hours | 7/11/2020 0:00 |
| C | -100 | < 0 Hours | 7/10/2020 0:00 |
| C | -250 | < 0 Hours | 7/9/2020 0:00 |
| D | 200 | > 100 Hours | 7/12/2020 0:00 |
| D | 100 | 25 - 100 Hours | 7/11/2020 0:00 |
| D | 20 | 0 - 24 Hours | 7/10/2020 0:00 |
| D | -5 | < 0 Hours | 7/9/2020 0:00 |
| D | -150 | < 0 Hours | 7/8/2020 0:00 |
| E | 22 | 0 - 24 Hours | 7/12/2020 0:00 |
| E | 5 | 0 - 24 Hours | 7/11/2020 0:00 |
| E | 2 | 0 - 24 Hours | 7/10/2020 0:00 |
| E | -15 | < 0 Hours | 7/9/2020 0:00 |
| E | -25 | < 0 Hours | 7/8/2020 0:00 |
| bin | binLegend |
| 1 | < 0 Hours |
| 2 | 0 - 24 Hours |
| 3 | 25 - 100 Hours |
| 4 | > 100 Hours |
18 Replies
- amitchandak
Super User
ravitejaballa , You can try measures like
m1 =lastnonblankvalue(Table[timestamp],max(Table[binLegendColumn]))
m2 = lastnonblankvalue(Table[timestamp],max(Table[lastValueHours]))
ravitejaballaThanks for reply,
I already have these measure in my report.
Using this measure, i want to get count of number of deviceID for each legend.
Like below.
Hi ravitejaballa ,
Based on your Data No of Distinct Device Id are as below.
In such case you can just use.
(DISTINCTCOUNT(deviceHours[DeviceID])How did you get 1 , 2, 1, 1 .
Let me know if I am missing something.
Regards
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
- v-diye-msft
Community Support
If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly. If not, please kindly elaborate more. thanks!
