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
PC2790's avatar
PC2790
Community Champion
4 years ago

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'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!