Forum Discussion
How to count rows between two columns
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
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.