power bi countrows between range

[Aadwark]

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

[amitchandak]

@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/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

[Aadwark]

I 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

[amitchandak · Accepted solution]

@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

[Aadwark]

Awesome, it was working

Thank you so much