count of grouped information

[praiselyabraham]

We are trying to achieve something like below.

Based on the sum of MissedInterval for a meter id, we need to group the meter into a band and then the count of meters falling into each band range defined. The challenge is when changing the slicer/filter.

For ex: 1) when selecting a range of 01-Jan to 10-Jan, meter ID=1 should fall into band of “1-3 Reads” as sum of missed interval=3.                

             2)  If selected a range of 01-Jan to 15-Jan, meter ID=1 should fall into band of “10+ Reads” as sum of missed interval=11, similar for rest of the meters.

Dummy input for scenario explained

Meter Info table
MeterUniqueNumber	IntervalsCollected	MissedIntervals	IntervalDate	Band
1	12	3	03-01-2018
1	7	8	15-01-2018
1	10	5	27-01-2018
1	14	1	28-01-2018
2	13	2	15-01-2018
2	15	0	27-01-2018
Band info table
Min	Max	Band
-5	0	No Missing
0	1	1 Read
2	10	1-10 Read
11	10000	10+ Read

Expected output:

For Scenario1( 01-Jan to 10 Jan)			For Scenario2( 01-Jan to 15 Jan)
Band	No of meters		Band	No of meters
No Missing	0		No Missing	0
1 Read	0		1 Read	0
1-10 Read	1		1-10 Read	1
10+ Read	0		10+ Read	1

[v-qiuyu-msft · Accepted solution]

Hi @praiselyabraham,

You can create a measure below: 

No of meters = IF(SUM('Meter Info table'[MissedIntervals])>=MAX('Band info table'[Min])&& SUM('Meter Info table'[MissedIntervals])<=MAX('Band info table'[Max]),1,0)

Best Regards,
Qiuyun Yu