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 m...
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!

     

     

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