Count devices depending on their most severe state in a table of messages

[knre]

Hello Community,

I have the following challenge concerning DAX with the desktop application of Power BI:

My table contains messages from different devices. There might be multiple messages from one device and each message has a dedicated severity level (1=warning, 2=critical, 3=error).

Based on this table I want to find out two things with measures/DAX:

• Quantity of devices that only have messages with severity level 1 (no messages with severity level 2 or 3)
• Quantity of devices that have messages with severity level 2 (and maybe with severity level 1 but not with severity level 3)

Example table:

device_id	message_id	severity_level	message_title
111	1_1	1	warning
222	2_1	1	warning
222	2_2	2	critical
333	3_1	1	warning
333	3_2	1	warning
333	3_3	1	warning
333	3_4	3	error
444	4_1	3	error
555	5_1	2	critical
555	5_2	3	error
555	5_3	3	error
666	6_1	2	critical
777	7_1	1	warning
777	7_2	1	warning
777	7_3	1	warning
777	7_4	1	warning

The quantity results of this example table should be:

• Quantity of devices with maximum severity level 1 (warning): 2 (111, 777)
• Quantity of devices with maximum severity level 2 (critical): 2 (222, 666)
• Quantity of devices with severity level 3 (error): 3 (333, 444, 555) => easy with filter and distinct count 😊

Best regards and thank you for your support.

knre

[Whitewater100]

Hi:

For the Warning piece you could try:(as Calc Col's) The first Calc Col just shows how many entries per device and is referred to in the next CC.:

Freq 1 (entries/device)=

var device = 'Table'[device_id]

var severity = 'Table'[severity_level]

return

COUNTROWS(FILTER(ALL('Table'),

'Table'[Device_id] = device

&& 'Table'[severity_level] = severity))

Severe CT = IF('Table'[severity_level]=1 && 'Table'[Freq 1] = 'Table'[Freq],"Warning",BLANK())

I'm having trouble with the second criteria. I like the methode johnt75 is doing but not sure with this example.

[johnt75 · Accepted solution]

You could try something like

Num max level 1 =
var level1 = CALCULATETABLE( VALUES( 'Table'[device id]), 'Table'[severity] = 1)
var level3 = CALCULATETABLE( VALUES( 'Table'[device id]), 'Table'[severity] = 3)
return COUNTROWS( EXCEPT( level1, level3 ) )

Num max level 2 =
var level2 = CALCULATETABLE( VALUES( 'Table'[device id]), 'Table'[severity] = 2)
var level3 = CALCULATETABLE( VALUES( 'Table'[device id]), 'Table'[severity] = 3)
return COUNTROWS( EXCEPT( level2, level3 ) )

[knre]

Nice, that's it 🙂 Thank you!
Just one thing I had to change for Num max level 1 at var level3:

var level23 = CALCULATETABLE( VALUES( 'Table'[device id]), 'Table'[severity] = 2 || 'Table'[severity] = 3)