Forum Discussion

jd1806's avatar
jd1806
New Member
2 years ago

How to count rows between two columns

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 sho...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
jd1806
New Member
2 years ago

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.

 

CompanyDate 1Date 2 
ABC1/11/20231/11/2023
DEF2/11/20235/11/2023
Alpha 3/11/2023
Beta4/11/20234/11/2023
Gemma15/11/202317/11/2023
Beg6/11/2023 
KLM7/11/20237/11/2023
HGD14/11/20238/11/2023
SEV9/11/20239/11/2023
NEV10/11/202310/11/2023
PQR11/11/202317/11/2023
UIO 12/11/2023
PRS13/11/202313/11/2023
Jik14/11/202314/11/2023
Has15/11/2023 
Dlm16/11/202316/11/2023
Wrt 13/11/2023
Yuf16/11/202318/11/2023

 

Hope this explains better.

 

Thank you in advance.

 

Kind Regards,

JD

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.