Forum Discussion

Oros's avatar
Oros
Post Prodigy
2 years ago
Solved

COUNT rows with filter

Hello,
 
How do you create a measure to COUNT many rows have zeros (in the VARIANCE column), 
but excluding the zero IF 
INVENTORY=0
and
COUNTED=0
 
In the example below, the measure should only have a count of 2 zeros instead of 3.    
 

Thanks

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    Assuming Inventory, Counted and Variance are measures that you have written, write this measure

    Measure = countrows(filter(Values(Data[Product]),[Inventory]<>0&&[Counted]<>0&&[Valiance]=0))

    Hope this helps.

11 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    2 years ago

    MEASURE =
    SWITCH (
    TRUE(),
    SUM ( table[inventory] ) = 0
    && SUM ( table[counted] ) = 0, 0,
    SUM ( table[variance] ) = 0, COUNTROWS ( table ),
    0
    )

    • Oros's avatar
      Oros
      Post Prodigy
      2 years ago

      Hi vanessafvg ,

       

      Thanks for your reply.

       

      The Switch measure does not work at all.  Is it because the Inventory, Counted and Variance are all measures in which the Inventory and Counted measures have blank values?

      • vanessafvg's avatar
        vanessafvg
        Community Champion
        2 years ago

        hi, if you need comprehensive help you really need to provide adequate sample data in text form.  one can't posssibly  consider aall scenarios and you have given limited information.

         

         

        the quality of the solutions is based on the information given, in data there are so many nuances so being explicit with the information will = a good solution.

         

        also what makes up those measures, is there column data you can provide with the measure code,  please provide appropriate dummy data in text form if you can't provide the real thing.

         

        thanks!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Assuming Inventory, Counted and Variance are measures that you have written, write this measure

    Measure = countrows(filter(Values(Data[Product]),[Inventory]<>0&&[Counted]<>0&&[Valiance]=0))

    Hope this helps.

    • Oros's avatar
      Oros
      Post Prodigy
      2 years ago

      Hi Ashish_Mathur ,

       

      Thanks you for your quick reply.

       

      The Inventory, Counted and Variance are all measures.  I noticed that your measure does not count the zeros correctly.  Is it because the Inventory and the Counted have blank values?

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        2 years ago

        You are welcome.  If they have blanks, then why did you mention/show 0 in your original post.  Try this revised measure

        Measure = countrows(filter(Values(Data[Product]),[Inventory]<>blank()&&[Counted]<>blank()&&[Variance]=0))

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    Oros 

    measure = 

    var ds = 

    filter ( 

    tabl_name, 

    tbl_name[inventory_col_name] +  tbl_name[counted_col_name] = 0 && tbl_name[variance_col_name] = 0 )

     

    return countrows(ds) 

     

    let me know if this helps .

     

     

    If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
    It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠 

    • Oros's avatar
      Oros
      Post Prodigy
      2 years ago

      Hi Daniel29195 ,

       

      Thank you for your reply.  I am getting this error.

      The syntax for ')' is incorrect. (DAX(var ds = filter (