Sum / Count of Measure Value

[Dayna]

Hello,

Ultimately, I want a count of servers where a record in a related table doesn't exist. So far, I've done the inverse, I calculate if we've done a test with the following:

CountOfSystemsTestedALL = 
CALCULATE (
    DISTINCTCOUNT ( 'DR Server'[Id] ),
    FILTER ( 'DR TestingLog', 'DR TestingLog'[TestDate] <> BLANK () ), FILTER(ALLSELECTED('DR Server'), 'DR Server'[ServerStatus] = "LIVE")
)

Then I work out the opposite:

NotTestedFlag = if([CountOfSystemsTestedALL]=1,0,1)

This basically gives me a value of 1 against all servers where we haven't tested it. All OK so far..

What I want, feels really basic, is a sum / count of these records. i.e. if there's 20 servers where the NotTestedFlag is 1, I want to sum and get a value of 20. Everything I do doesn't seem to work!

Many thanks for your help!

Kind Regards,

Dayna

[Anonymous · Accepted solution]

Hi @Dayna 

Is it maybe possible to do something like below?

Sum of tested servers =
SUMX (
    SUMMARIZE ( 'DR Server', 'DR Server'[Id], "Value", [NotTestedFlag] ),
    [Value]
)

Pct tested = [Sum of tested servers] / DISTINCTCOUNT('DR Server'[Id])

The summarize should return a list where your measure [NotTestedFlag] is calculated for each server ID and then it is summed by the SUMX.

I'm assuming that the denomiator your %-measure is the number of servers.

If this works then please mark it as the accepted solution.

[Anonymous]

You're welcome.

If you have tried to use DAX studio then you can see the table which summarize creates by writing the following:

EVALUATE
SUMMARIZE ( 'DR Server', 'DR Server'[Id], "Value", [NotTestedFlag] )

The "Value" can be though of as a calculated column containing the measure NotTestedFlag, whereas [Value] is refering to this exact column.

The measure is therefore the sum of a calculated column in a flexible table.

[Dayna]

That seemed to work! Thank you! Can you explain to me how the "value" and [value] works in that, please?