Forum Discussion
PowerBeeEye
Microsoft Employee
6 years agocount 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...
- 6 years ago
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.
- 6 years ago
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 ) )
smpa01
Community Champion
6 years agoPowerBeeEye it might work
Measure = CALCULATE(COUNTROWS('Table'), ALL('Table'[ServiceName]))