How can I count rows containing # 1 only

[Marcegon]

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!

[Anonymous · Accepted solution]

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.

[Marcegon]

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]

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!

[sevenhills]

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]

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)