Filtering out '0' in two columns to bring back results only with a numbers

[Anonymous]

 Hi

I'm trying write a measure to get results back from a table (looking at two columns) which counts only the results which have a number in one or both columns , ignoring anything with '0' in both columns. If I was using excel I would filter out the two columns which have '0' in both cells, leaving me with the columns that have a number in both, or a number in one and '0' in the other. I do not want to include results that have a zero in both columns only. The measure I have written is

= COUNTROWS(FILTER('Cycle Counts','Cycle Counts'[Cycle Count Qty. (Calculated)]<>0) & COUNTROWS(FILTER('Cycle Counts','Cycle Counts'[Cycle Count Qty. (Phys. Inventory)]<>0)))

I get an error back to say the parameter is not correct and also that'multiple columns cannot be to a scalar value.

I am very new to this hence the multiple errors and have zero practice at writing measures

[camargos88]

Hi @Anonymous ,

Try it using COUNTX:

https://docs.microsoft.com/pt-br/dax/countx-function-dax

[Anonymous]

Hi- unfortunately I don't know if. this would have worked as the link looked like it was in Spanish. I did get a solution though, and thank you for replying.

[mahoneypat · Accepted solution]

Please try this measure out.

= COUNTROWS(FILTER('Cycle Counts','Cycle Counts'[Cycle Count Qty. (Calculated)]<>0 && 'Cycle Counts'[Cycle Count Qty. (Phys. Inventory)]<>0))

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hi Pat

Many thanks, this one worked!

[FabricLearnFabr]

How do you use this measure to filter out the data?