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 should be 31.

Date 1Date 2 
1/11/20231/11/2023
2/11/20235/11/2023
 3/11/2023
4/11/20234/11/2023
15/11/202317/11/2023
6/11/2023 
7/11/20237/11/2023
14/11/20238/11/2023
9/11/20239/11/2023
10/11/202310/11/2023
11/11/202317/11/2023
 12/11/2023
13/11/202313/11/2023
14/11/202314/11/2023
15/11/2023 
16/11/202316/11/2023
 13/11/2023
16/11/202318/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

Need Help

3 Replies

  • 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.