Forum Discussion

Marcegon's avatar
Marcegon
Icon for Helper II rankHelper 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.

Marcegon's avatar
Marcegon
Icon for Helper II rankHelper 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
    Icon for Super User rankSuper 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!