Count Events with Conditions

[nelson777]

Hi, legends,

I have a report that shows interruption alert events for each device and its client by date. 

I need to count in this way:

Client A has 15 devices

Client B has 3 devices

Client C has 1 device

On the 26th of April, the system detected 50 interruption alerts

Client A 30   across 10 devices

Client B 15   across 1 device

Client C 05   across 1 device 

So, I would like to count it as 1 different event just if more than one device within the client had interruptions.

26th of April = 1 Event (just Client A will be counted because even though Client B and C had had interruption events, those were in just one device.

These are the results I'm looking to achieve:

[PC2790]

How does your data set look like?

Based on your data, you can use the concept of grouping or summarize and get  the distinct count to achieve this.

[nelson777]

It basically looks like this

[PC2790]

Ideally you should have a date table which you should join with your main table to perform the calculations and analysis.

Create two measures as

No of clients = Count(Clients)

No of Devices = Count (Devices)

Now create a matrix with Client and Devices in Rows section, and Date in Columnssection and the values would contain your measures-No of clients & No of Devices

In the row header formatting of the matrix, switch off the +/- icon and stepped layout to have look nd feel of a table.

See if this works for you.

If not please give sample date in a format that can be copy pasted. I'll give you a complete solution usng that.

[nelson777]

I've updated the original question with Excel screenshots of the results I'm looking for.

I'm also trying this DAX:

Measure =

VAR vAlertsSummary = SUMMARIZE(f_VPNUptime,d_Calendar[Date],f_VPNUptime[Client], "Devices Qty",

DISTINCTCOUNT(f_VPNUptime[Device Name])

)

VAR vFilteredAlerts =

FILTER(vAlertsSummary,

[Devices Qty] > 1

)

RETURN

COUNTROWS(vFilteredAlerts)

But I'm not sure if I'm getting the expected results... still analysing.

Thanks!