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 o...
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.

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!

      • Oros's avatar
        Oros
        Post Prodigy
        2 years ago

        Hi vanessafvg,

         

        I'll make it clear next time.  Thanks for the help. 🙂