powerbidaxpowerbi-desktopmeasure

Dynamically changing the band based on aggregate chosen from slicer


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]))

Detailed Image of Expected Output Attached


Solution

  • you can try to create a alarmband table like below.

    enter image description here

    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
    

    enter image description here