topic Percentage of Non-Null values in a single column in DAX Commands and Tips

Percentage of Non-Null values in a single column

WBADAM03 — Wed, 28 Jun 2023 16:27:11 GMT

Hi all,

I'm still bad at DAX and this problem seems simple but I've tried a few approachs and can't get it working.

I have a table, let's say Table1 and I have a column, let's say Score.

I need to find a way to show a percantage in a matrix visual on a dashboard of table1 of all nun-null values.

I got a dax expression to work with blank functions, but I had to convert every null value to a blank value to do this, and I have to make this measure 4 times over.

I got something to technically work, but it gives me values like 103% if I try to show the percantage as % of grand total.

PercentReporting = DIVIDE(COUNTROWS(FILTER('Table1','Table1'[Score] = "null")),COUNTROWS('Score')).

Is there any easier way to do this that won't return weird values like 103% ?

Re: Percentage of Non-Null values in a single column

v-zhangti — Fri, 30 Jun 2023 02:28:57 GMT

Hi, WBADAM03

You can try:

NonBlankPercentage = DIVIDE ( COUNTROWS ( FILTER ( 'Table1', NOT ( ISBLANK ( 'Table1'[Score] ) ) ) ), COUNTROWS ( 'Table1' ) )

Or:

NonBlankPercentage = DIVIDE( DISTINCTCOUNTNOBLANK(Table1[Score]), COUNTROWS('Table1'))

Does this solve your problem? If not, please provide example data.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Percentage of Non-Null values in a single column

WBADAM03 — Fri, 30 Jun 2023 14:09:12 GMT

Hey there, that's prettty similiar to what I used.

It didn't intentionally work because the way SQL returned my data was "null" and not blank. So the notblank function didn't work.

I ended up changing this to output blank values instead of a null text string, that allowed the nonblank function to work.

I'm going to mark your answer as the solution because it's what I ended up using.

Thanks