Forum Discussion
power bi countrows between range
Hi guys,
Im looking for a solution to count how many snapshots do we have which are between 0-5, 5-15 and over 15 days old.
I could do it easily in xls like : =COUNTIFS(Actual!I:I,"EMEA",Actual!C:C,">=5",Actual!C:C,"<=15")
But i have spent million hours already to figure out how should i do in Power but i still have no idea, i have tried this: CALCULATE(COUNT(Actual[Snapshot Age (Days)]),'Actual'[Snapshot Age (Days)] <5 it was working if i have only 1 variables,
but if i wants to have it between the range its not working :
CALCULATE(COUNT(Actual[Snapshot Age (Days)]),'Actual'[Snapshot Age (Days)] <5,Actual[Snapshot Age (Days)] >15)
If you can give me some advice it will be much presiated.
Thanks
Aadwark , you can simply create a new column
If [Snapshot Age] is a column and then you can use count of any column
Switch(true(),
[Snapshot Age] < 5, " Days < 5" ,
[Snapshot Age] < 15, " Days 5 - 15" ,
" >15" )
If [Snapshot Age] is measure follow the link I have given in last update
4 Replies
Aadwark , Not very clear. Bust seems like you need a bucketing on measure
Have a measure
COUNT(Actual[Snapshot Age (Days)])
and follow this concept
Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626
Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-kI will check thanks,
but i also try to make it more clear 🙂
So i have a table with the data like this:
VM name Snapshot Age VM1 13 VM2 144 VM3 24 VM4 24 VM5 221 VM6 285 VM7 290 VM8 156 And in xls i could count the number of VMs which has snapshots, and those snaps are between range (old).
Like we have 13 VM where their snapshot are between 0-5 days old
than 7 VM where their snapshot are between 5-15 days old
than 56 VM where their snapshot are between 5-15 days old
EMEA Days Number 5 13 5-15 7 15 56 I also could make it done in Power when i have used a single xls file, but all of my calculation was inside the xls so the power only visualized, but because i have multiple location i would automate it and grab all xls from a folder, therefore i cannot have any calculation inside of the xls files so it has to be done in Power.
i hope its more clear now 😛
Thanks
Aadwark , you can simply create a new column
If [Snapshot Age] is a column and then you can use count of any column
Switch(true(),
[Snapshot Age] < 5, " Days < 5" ,
[Snapshot Age] < 15, " Days 5 - 15" ,
" >15" )
If [Snapshot Age] is measure follow the link I have given in last update
