Forum Discussion
Helper VSum / Count of Measure Value
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
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.
3 Replies
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.
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.
DaynaThat seemed to work! Thank you! Can you explain to me how the "value" and [value] works in that, please?