There is a table named customer alarms, we have customers, alarm duration and count of alarm against every alarmduration
Customer | AlarmDuration | CountofAlarm |
---|---|---|
C01 | 15m | 3 |
c01 | 15m-3h | 4 |
There is a separate table named AlarmBands that has values 0
, 1-5
,6-10
, 10+
which specify the band based on count of alarms
AlarmBands |
---|
0 |
1-5 |
6-10 |
10+ |
there is a filter that has alarmduration (15m
, 15m to 3h
,3h to 7h
,7h+
)
this is the same column we have in customer's table
based on the values selected from the filter, it should sum up the customers alarms and then choose a band value from AlarmBands table.
For example: Customer C01 has 3 alarm of 15m and 4 alarms of 15mto 3h duration when the user selects 15m from the filter. this customer should be counted in band 1-5 because there were 3alarms of 15m and 3 lies in between 1-5
but if the user selected 15m and 15m-3h (both) from filter. Total alarms =7, so this customer should be counted in band 6 to 10
I am unable to achieve this in power BI. Any leads on it would be of great help
I need AlarmBands on X axis and count of customers on Y axis. connected to a filter of Alarm Duration
I have tried to accomplish it with the below dax
Measure=
var _a1=ALLSELECTED('Table'[AlarmDuration])
var _a2= SUMMARIZE(CALCULATETABLE('Table',FILTER('Table',CONTAINSROW(_a1,'Table'[AlarmDuration]))),'Table'[Customer],"CountOFAlarm",SUM('Table'[CountofAlarm]))
var _a3=ADDCOLUMNS(_a2,"AlarmBand", IF([abc]=0,0,[abc]>=1 && [abc]<=5,"1-5",[abc]>5 && [abc]<=10,"6 to10","10+"))
var _a4= COUNT(_a2[{Customer],USERELATIONSHIP(_a3[AlarmBand],ALrmB[AlarmBand]))
you can try to create a alarmband table like below.
then create a measure
MEASURE =
VAR _list =
DISTINCT ( 'customer alarms'[AlarmDuration] )
VAR _tbl =
SUMMARIZE (
FILTER ( 'customer alarms', 'customer alarms'[AlarmDuration] IN _list ),
'customer alarms'[Customer],
"total", SUM ( 'customer alarms'[BandwiseAlarmCount] )
)
RETURN
COUNTROWS (
FILTER (
_tbl,
[total] >= MAX ( 'AlarmBands'[Left] )
&& [total] <= MAX ( AlarmBands[Right] )
)
) + 0