Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

COUNTROWS FILTER - Multiple Values

Hello, 

 

I have a column that has a number of 1's and 0's and I want to count how many 1's there are and 0's there are as they are base don a different column. 

 

I have tried to use the below measure but for some reason it doesn't work... please assist:

 

Measure = CALCULATE(
                        COUNTROWS(Table_query),
                        FILTER(Table_query, Table_query[Recharge Decision] = "1"),
                        FILTER(Table_query,Table_query[Recharge Decision] ="0"))

 

Need Help
Show and Tell
Tips and Tricks
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi Anonymous ，

     

    You need to split your measure into 2,1 is for calculating the number of 1's and the other is for the number of  0's:

    If the data of the column is a text type,using:

     

    Measure for 1 = CALCULATE(
                        COUNTROWS(Table_query),
                        FILTER(Table_query, Table_query[Recharge Decision] = "1")
    Measure for 0= CALCULATE(
                        COUNTROWS(Table_query),
                        FILTER(Table_query, Table_query[Recharge Decision] ="0"),

     

    If the data of the column is a whole number type,using:

     

    Measure for 1 = CALCULATE(
                        COUNTROWS(Table_query),
                        FILTER(Table_query, Table_query[Recharge Decision] = 1)
    Measure for 1 = CALCULATE(
                        COUNTROWS(Table_query),
                        FILTER(Table_query, Table_query[Recharge Decision] = 0)

     

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!

     

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    If there other values then 1 and 0 then

    Measure = CALCULATE(
    COUNTROWS(Table_query),
    FILTER(Table_query, Table_query[Recharge Decision] = "1" || Table_query[Recharge Decision] ="0"))

     

    Other wise create 2 measures

    Measure1 = CALCULATE(
    COUNTROWS(Table_query),
    FILTER(Table_query, Table_query[Recharge Decision] = "1" ))

     

    Measure2 = CALCULATE(
    COUNTROWS(Table_query),
    FILTER(Table_query,  Table_query[Recharge Decision] ="0"))

     

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    6 years ago

    Hi Anonymous ，

     

    You need to split your measure into 2,1 is for calculating the number of 1's and the other is for the number of  0's:

    If the data of the column is a text type,using:

     

    Measure for 1 = CALCULATE(
                        COUNTROWS(Table_query),
                        FILTER(Table_query, Table_query[Recharge Decision] = "1")
    Measure for 0= CALCULATE(
                        COUNTROWS(Table_query),
                        FILTER(Table_query, Table_query[Recharge Decision] ="0"),

     

    If the data of the column is a whole number type,using:

     

    Measure for 1 = CALCULATE(
                        COUNTROWS(Table_query),
                        FILTER(Table_query, Table_query[Recharge Decision] = 1)
    Measure for 1 = CALCULATE(
                        COUNTROWS(Table_query),
                        FILTER(Table_query, Table_query[Recharge Decision] = 0)

     

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!

     

     

  • Justas4478's avatar
    Justas4478
    Post Prodigy
    2 years ago

    amitchandak Hi I am trying to use these measures to calculate rows for my stock.
    But it is bringing back not what I expect.

    Count store stock = CALCULATE(COUNTROWS('Store Stock'),FILTER('Store Stock','Store Stock'[Store Stock Qty] = 0))

    I know that total amount of row are 4572, but measure brings back only 4204.

    Ideally I would like to avoid using filter inside measure so that I could use visual filter so used could filter on any value.

     
    I have other measure that, but it breaks when I add stores to the table.

    Products stock at all stores =
    VAR summarybyproduct =
    ADDCOLUMNS(
        VALUES('Product'[SKU Number]),
        "Total stock",
       CALCULATE('Store Stock'[Store Stock Qty])
        )

    VAR productswithzero =
    FILTER(summarybyproduct,[Total stock])
    RETURN
    COUNTROWS(productswithzero)



    I attached sample file if it helps
    https://we.tl/t-LVpPk1HQoq