topic Re: Filtering out '0' in two columns to bring back results only with a numbers in DAX Commands and Tips

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

Anonymous — Thu, 14 May 2020 12:26:50 GMT

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

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

camargos88 — Thu, 14 May 2020 13:36:18 GMT

Hi @Anonymous ,

Try it using COUNTX:

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

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

mahoneypat — Thu, 14 May 2020 20:30:46 GMT

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

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

Anonymous — Fri, 15 May 2020 09:33:19 GMT

Hi Pat

Many thanks, this one worked!

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

Anonymous — Fri, 15 May 2020 09:35:04 GMT

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.

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

FabricLearnFabr — Fri, 06 Dec 2024 07:27:42 GMT

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