Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |