Forum Discussion

PowerBeeEye's avatar
PowerBeeEye
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago
Solved

count with and without filter

I am working with the data below - table of organizations, servernames and services installed on each server:

Query_Server_Services

CompanyforestSystemNameServiceName
Contosocontoso.comContosoSrv1svc1
Contosocontoso.comContosoSrv1svc2
Litwarelitware.netLitwareSrv1svc1
LitwareLitware.caLitwareSrv1svc2
Fabrikamfabrikam.orgFabrikamsrv1svc1
Fabrikamfabrikam.orgFabrikamsrv2svc2

 

I have slicers for company name, forest name and service name.

If I filter for company, forest and service, how can I get the number of servers in that company and forest WITHOUT that service?

I have been able to plot the number of servers WITH the service using 

= CALCULATE (DISTINCTCOUNT(Query_Server_Services[SystemName]))

Any help is appreciated!

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    PowerBeeEye 

    For your count with service you don't need the calculate.

    Count with service = DISTINCTCOUNT ( 'Table'[SystemName] )

    Then we can use that one and some filtering to calculate those without the selected service.

    Count without service = 
CALCULATE(
    DISTINCTCOUNT('Table'[SystemName]),ALLEXCEPT('Table','Table'[Company],'Table'[forest])) - [Count with service]

    This counts the total number of systems for the selected [Company] and [Forest] using any service the subtracts the count for the selected service leaving us with the number not using the service.

    My sample file is attached for you to look at.

     

     

  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    PowerBeeEye 

    That was an interesting one.  This should get you what you are looking for.

    Systems without service = 
VAR _Systems = VALUES ( 'Table'[SystemName] )
VAR _AllSystems = ALL ( 'Table'[SystemName] )
VAR _Missing = EXCEPT(_AllSystems,_Systems)
RETURN 
CALCULATE ( 
    CONCATENATEX ( 
        VALUES ('Table'[SystemName]), 'Table'[SystemName],", " ),
        ALL ( 'Table'[ServiceName] ),
        'Table'[SystemName] IN ( _Missing ) 
)

5 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    6 years ago

    PowerBeeEye 

    For your count with service you don't need the calculate.

    Count with service = DISTINCTCOUNT ( 'Table'[SystemName] )

    Then we can use that one and some filtering to calculate those without the selected service.

    Count without service = 
CALCULATE(
    DISTINCTCOUNT('Table'[SystemName]),ALLEXCEPT('Table','Table'[Company],'Table'[forest])) - [Count with service]

    This counts the total number of systems for the selected [Company] and [Forest] using any service the subtracts the count for the selected service leaving us with the number not using the service.

    My sample file is attached for you to look at.

     

     

    • PowerBeeEye's avatar
      PowerBeeEye
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      Quick question - how can I get a list of servers NOT running a service (SVC2, for example).

      Since there are no rows indicating that SVR2 is NOT running SVC2, I understand this is not possible by default.

      But would be possible to find "find all servers running SVC1 (assuming it runs on all servers) but NOT SVC2 (selected using the slicer)?

       

      Thanks!

      • jdbuchanan71's avatar
        jdbuchanan71
        Icon for Super User rankSuper User
        6 years ago

        PowerBeeEye 

        That was an interesting one.  This should get you what you are looking for.

        Systems without service = 
VAR _Systems = VALUES ( 'Table'[SystemName] )
VAR _AllSystems = ALL ( 'Table'[SystemName] )
VAR _Missing = EXCEPT(_AllSystems,_Systems)
RETURN 
CALCULATE ( 
    CONCATENATEX ( 
        VALUES ('Table'[SystemName]), 'Table'[SystemName],", " ),
        ALL ( 'Table'[ServiceName] ),
        'Table'[SystemName] IN ( _Missing ) 
)