cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Post Partisan

## 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

1 ACCEPTED SOLUTION
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
11 REPLIES 11
Super User

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! 🤠

Post Partisan

Hi @Daniel29195 ,

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

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Post Partisan

This works!  Thanks a lot!

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Post Partisan

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?

Super User

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))

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

Post Partisan

Hi @vanessafvg ,

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?

Super User

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!

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

Post Partisan

Hi @vanessafvg,

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors