Forum Discussion

Marcegon's avatar
Marcegon
Helper II
2 years ago
Solved

How can I count rows containing # 1 only

Hi, I have a query with (3) columns "Name, attribute, and value".  I need the total for only those rows containing # 1. 

How could I achieve this?

 

Thank you!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Marcegon , Hello sevenhills , thank you for your prompt reply!
    Try the measure as shown below:

    CountRowsWithOnlyOnes =
CALCULATE(
   COUNTROWS('YourTable'),
   FILTER(
       'YourTable',
       AND(
           'YourTable'[Column1] = 1,
           'YourTable'[Column2] = 1,
           'YourTable'[Column3] = 1,
           //you can add other conditions
       )
   )
)

    Please feel free to respond if there is any misunderstanding about your question.

    Best regards,

    Joyce

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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Marcegon , Hello sevenhills , thank you for your prompt reply!
    Try the measure as shown below:

    CountRowsWithOnlyOnes =
CALCULATE(
   COUNTROWS('YourTable'),
   FILTER(
       'YourTable',
       AND(
           'YourTable'[Column1] = 1,
           'YourTable'[Column2] = 1,
           'YourTable'[Column3] = 1,
           //you can add other conditions
       )
   )
)

    Please feel free to respond if there is any misunderstanding about your question.

    Best regards,

    Joyce

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

  • sevenhills's avatar
    sevenhills
    Super User
    2 years ago

    Try this? It was not clear whether you want to filter 1 or "# 1", provided both...

     

     

    CountRowsWithValue1 = 
COUNTROWS(FILTER('Table 1', 'Table 1'[Value] = 1))

CountRowsWithValue1 = 
COUNTROWS(FILTER('Table 1', 'Table 1'[Value] = "# 1"))

     

    • Marcegon's avatar
      Marcegon
      Helper II
      2 years ago

      Thank you. The visual needs to show the number of issues per field; therefore it needs to count # 1s.  However, when there is other number, I want it to show "0" (see visual below)

       

       

  • Marcegon's avatar
    Marcegon
    Helper II
    2 years ago

    Thank you!  I forgot to mention that I need to show the rows with other values as "0".  The visual should should attributes with a value of "0" when there is no attribute with a value of 1."  How can I achieve this?

     

    • sevenhills's avatar
      sevenhills
      Super User
      2 years ago
      CountRowsWithValue1 = 
COALESCE(COUNTROWS(FILTER('Table 1', 'Table 1'[Value] = 1)), 0)

CountRowsWithValue1 = 
COALESCE(COUNTROWS(FILTER('Table 1', 'Table 1'[Value] = "# 1")), 0)

       


      Using COALESCE, you can achieve it. See if the above syntax for your needs!