Exclude NULL values from VAR Measures

[jamesclark458]

Hi folks,

I have a series of visuals that calculate a Net Promoter Score style output. 

I was given elsewhere in this forum the below measure, you will note that at the bottom that NULL values are assigned a score of 0. 

This is skewing the results, and I am wondering how I exclude NULL values from the calculation?

Any help would be greatly appreciated,

Thanks

Jamie 

NPS Score =

//be sure you have a question in your survey asking "How likely would you be to recommend us?" with a scale of 0-10

VAR detractors = CALCULATE(

           COUNTROWS('Repository'),

                     FILTER('Repository',

                     'Repository'[NPS]<=6)

           )

//don't forget to make your NPS question column into a whole number data type

VAR passives = CALCULATE(

           COUNTROWS('Repository'),

                     FILTER('Repository',

                     'Repository'[NPS]=7)

           )

           +CALCULATE(

                     COUNTROWS('Repository'),

                      FILTER('Repository',

                     'Repository'[NPS]=8)

           )

VAR promoters = CALCULATE(

           COUNTROWS('Repository'),

                     FILTER('Repository',

                     'Repository'[NPS]>=9)

           )

VAR total = COUNTROWS('Repository')

VAR score = (promoters/total*100)-(detractors/total*100)

RETURN If(

                     ISBLANK(score),

                     0,

                     score

           )

[jamesclark458]

Hi @v-binbinyu-msft 

Thank you for your help with this - apologies it has taken so long for me to respond. 

This is in action now,

Jamie

[v-binbinyu-msft · Accepted solution]

Hi @jamesclark458 ,

Please try to filter the table that you want to exclude null value, then use this new table to calculate, like below dax formula, please refer:

var tmp= filter(All("Table Name"),[ColumnName]<>"null")

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.