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 Company forest SystemName ServiceName Contoso contoso.c...
  • jdbuchanan71's avatar
    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 ) 
    )