How to count rows between two columns

[jd1806]

Hi,

I want to count rows for two columns where if in a row both the column has value count as one, otherwise count seperately. See below table for example where the total count of both column should be 31.

Date 1	Date 2
1/11/2023	1/11/2023
2/11/2023	5/11/2023
	3/11/2023
4/11/2023	4/11/2023
15/11/2023	17/11/2023
6/11/2023
7/11/2023	7/11/2023
14/11/2023	8/11/2023
9/11/2023	9/11/2023
10/11/2023	10/11/2023
11/11/2023	17/11/2023
	12/11/2023
13/11/2023	13/11/2023
14/11/2023	14/11/2023
15/11/2023
16/11/2023	16/11/2023
	13/11/2023
16/11/2023	18/11/2023

Also, i want to get this count function in power query to be able to use as a card visual.

Thank you in advance.

Kind Regards,

JD

[Anonymous]

Hi @jd1806 ,

Based on your description, I created the same sample as yours and you may check the following results:

Total = 
var _one = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Date 1]=BLANK()||'Table'[Date 2 ]=BLANK()))

var _two = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Date 1]<>BLANK()&&'Table'[Date 2 ]<>BLANK()))

return VALUE(_one+_two*2)

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[jd1806]

Hi, 

Thank you for your response.

Apologies, but i did not ask the question correctly. I still need to count rows in both columns, where I want to count rows for two columns where if in a row both the column has value count as one, otherwise count seperately. See below table for example where the total count of both column should be 18. Because date 1 and date 2 column has common value 13 times and 5 (2 times single value in date 1 column and 3 times single value in Date 2 column) times they had individual values.

Company	Date 1	Date 2
ABC	1/11/2023	1/11/2023
DEF	2/11/2023	5/11/2023
Alpha		3/11/2023
Beta	4/11/2023	4/11/2023
Gemma	15/11/2023	17/11/2023
Beg	6/11/2023
KLM	7/11/2023	7/11/2023
HGD	14/11/2023	8/11/2023
SEV	9/11/2023	9/11/2023
NEV	10/11/2023	10/11/2023
PQR	11/11/2023	17/11/2023
UIO		12/11/2023
PRS	13/11/2023	13/11/2023
Jik	14/11/2023	14/11/2023
Has	15/11/2023
Dlm	16/11/2023	16/11/2023
Wrt		13/11/2023
Yuf	16/11/2023	18/11/2023

Hope this explains better.

Thank you in advance.

Kind Regards,

JD

[Anonymous]

Hi @jd1806 ,

Based on your description, I have modified the original expression and you can check the result below:

For new table:

Total = 
var _one = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Date 1]=BLANK()||'Table'[Date 2 ]=BLANK()))
var _two = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Date 1]<>BLANK()&&'Table'[Date 2 ]<>BLANK()))
return ADDCOLUMNS(ALL('Table'),"For one",_one,"For two",_two)

For measure:

Total = 
var _one = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Date 1]=BLANK()||'Table'[Date 2 ]=BLANK()))
var _two = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Date 1]<>BLANK()&&'Table'[Date 2 ]<>BLANK()))
return  IF(MAX('Table'[Date 1]) = BLANK()||MAX('Table'[Date 2 ]) =BLANK(),MAXX('Total','Total'[For one]),MAXX('Total','Total'[For two]))

Please feel free to correct me and provide more information if I have misunderstood you!

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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