Forum Discussion

nelson777's avatar
nelson777
Frequent Visitor
4 years ago

Count Events with Conditions

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 Clien...
Need Help
Tips and Tricks
Tutorial Requests
nelson777's avatar
nelson777
Frequent Visitor
4 years ago

It basically looks like this

 

 

PC2790's avatar
PC2790
Community Champion
4 years ago

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's avatar
    nelson777
    Frequent Visitor
    4 years ago

    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!