topic Re: COUNTROWS Calculation not working for Multiple filters. in DAX Commands and Tips

COUNTROWS Calculation not working for Multiple filters.

ScarlettBebb — Tue, 29 Aug 2023 10:36:08 GMT

Hello,

Thank you so much in advance if you response to this. I am trying to create a Count Rows column or Measure it doesn't matter which for this. I need to count the number of rows as a way of calucating the number of tested products/Lots per Month. I have a Date column called 'Attribute', A Product column, and a Lot column. This is an example of my Table.

Product	Lot	Attribute	Verdict
G111	1	05-Jan-16	1
G111	2	12-Feb-16	1
G111	3	04-Mar-16	1
G111	4	08-Apr-16	0
G111	5	14-May-16	0
G111	6	16-Jun-16	0
G111	7	17-Jul-16	0
G111	8	18-Aug-16	1
G111	9	26-Sep-16	1
G222	1	05-Jan-16	1
G222	2	12-Feb-16	1
G222	3	04-Mar-16	0
G222	4	08-Apr-16	0
G222	5	14-May-16	0
G222	6	16-Jun-16	1
G222	7	17-Jul-16	1
G222	8	18-Aug-16	1
G222	9	26-Sep-16	1
G333	1	05-Jan-16	1
G333	2	12-Feb-16	1
G333	3	04-Mar-16	0
G333	4	08-Apr-16	0
G333	5	14-May-16	0
G333	6	16-Jun-16	1
G333	7	17-Jul-16	1
G333	8	18-Aug-16	1
G333	9	26-Sep-16	1

I am trying to caluclate the number of tested batches by Product and Lot over 1 month per year using this formula which is not working.

Number of tested = COUNTROWS(

FILTER('FPA data combined'),

FILTER('FPA data combined'[Attribute],

FILTER('FPA data combined'[Product ],

FILTER('FPA data combined'[Lot]))

))

Thank you so much,

Scarlett

Re: COUNTROWS Calculation not working for Multiple filters.

ERD — Tue, 29 Aug 2023 10:52:45 GMT

Hi ScarlettBebb , do you have a separate Date table in your model? What are you trying to filter?

Measure = COUNTROWS('Table')

Re: COUNTROWS Calculation not working for Multiple filters.

ScarlettBebb — Tue, 29 Aug 2023 10:57:21 GMT

Hello ERD I have a date column in table. I am trying to count the numbers of Products tested per month so I have filtered 'Product' and the 'Lot' and then was trying the filter 'Date to 1 month'. So my new column should look like the one below 'Numbers Tested'.

Product	Lot	Attribute	Verdict	Numbers Tested
G111	1	05-Jan-16	1	1
G111	2	12-Feb-16	1	1
G111	3	04-Mar-16	1	1
G111	4	08-Apr-16	0	1
G111	5	14-May-16	0	1
G111	6	16-Jun-16	0	1
G111	7	17-Jul-16	0	1
G111	8	18-Aug-16	1	1
G111	9	26-Sep-16	1	1
G222	1	05-Jan-16	1	1
G222	2	12-Feb-16	1	1
G222	3	04-Mar-16	0	1
G222	4	08-Apr-16	0	1
G222	5	14-May-16	0	1
G222	6	16-Jun-16	1	1
G222	7	17-Jul-16	1	1
G222	8	18-Aug-16	1	1
G222	9	26-Sep-16	1	1
G333	1	05-Jan-16	1	1
G333	2	12-Feb-16	1	1
G333	3	04-Mar-16	0	1
G333	4	08-Apr-16	0	1
G333	5	14-May-16	0	1
G333	6	16-Jun-16	1	1
G333	7	17-Jul-16	1	1
G333	8	18-Aug-16	1	1
G333	9	26-Sep-16	1	1

Re: COUNTROWS Calculation not working for Multiple filters.

ERD — Tue, 29 Aug 2023 12:33:41 GMT

ScarlettBebb , you can try this calculated column formula:

Column = VAR product = 'Table'[Product] VAR lot = 'Table'[Lot] VAR month = MONTH ( 'Table'[Attribute] ) VAR year = YEAR ( 'Table'[Attribute] ) VAR t = FILTER ( 'Table', 'Table'[Product] = product && 'Table'[Lot] = lot && MONTH ( 'Table'[Attribute] ) = month && YEAR ( 'Table'[Attribute] ) = year ) RETURN COUNTROWS ( t )

Re: COUNTROWS Calculation not working for Multiple filters.

ScarlettBebb — Tue, 29 Aug 2023 13:09:05 GMT

ERD Thank you so much that worked perfectly 🙂