topic Re: Divide the count of rows from one column with the count of non-blank rows from another column. in DAX Commands and Tips

Divide the count of rows from one column with the count of non-blank rows from another column.

KandiBawa — Thu, 27 Feb 2025 00:51:19 GMT

Hi All,

I have 2 columns that I want to use here:

1. Comments: This is a text column where people have either put in a comment or left it blank.

2. Sentiment: This is also a text column with 4 possible values (Positive, Negative, Neutral, and NA)

I want to get the % of each sentiment type for all the non-blank comments.
I.e. (Count of Sentiment Type/Non-Blank comments)*100

I have used the following measures:

1. Total Comments = CALCULATE(COUNTROWS(Table1), Table1[Calendar Comments] <> BLANK())

On using this in a card visual it gives me the right value. I.e. 3675 non-blank comments.

2. Dynamic Sem Sentiment = DIVIDE(COUNTROWS(Table1), 3675)

Using this when I have hard coded the denominator gives me the right result (see screenshot 1).

But if I update the measure to make it dynamic I.e

Dynamic Sem Sentiment = DIVIDE(COUNTROWS(Table1), MeasureTable[Total Comments])

It gives me an incorrect result. (See screenshot 2)

Can you please explain why this is happening and how to fix this?

TIA

Re: Divide the count of rows from one column with the count of non-blank rows from another column.

BeaBF — Thu, 27 Feb 2025 07:56:34 GMT

KandiBawa Hi! Try with a one single measure:

Dynamic Sem Sentiment =
VAR TotalNonBlankComments = CALCULATE(COUNTROWS(Table1), Table1[Calendar Comments] <> BLANK())
RETURN
DIVIDE(COUNTROWS(Table1), TotalNonBlankComments)

BBF

Re: Divide the count of rows from one column with the count of non-blank rows from another column.

Anonymous — Tue, 04 Mar 2025 01:59:45 GMT

Hi KandiBawa ,

I think you can update [Total Comments] measure as below.

Total Comments = CALCULATE ( COUNTROWS ( Table1 ), FILTER ( ALLSELECTED ( Table1 ), Table1[Calendar Comments] <> BLANK () ) )

Best Regards,
Rico Zhou

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

Re: Divide the count of rows from one column with the count of non-blank rows from another column.

sjoerdvn — Tue, 04 Mar 2025 08:25:34 GMT

If I understand the requirement correctly, it's like this:

Dynamic Sem Sentiment = DIVIDE( CALCULATE(COUNTROWS(Table1), Table1[Calendar Comments] <> BLANK()) ,CALCULATE(COUNTROWS(Table1),ALL(Table1[Sentiment]), Table1[Calendar Comments] <> BLANK()) )