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.