COUNTROWS Calculation not working for Multiple filters.

[ScarlettBebb]

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

[ERD]

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

Measure = COUNTROWS('Table')

[ScarlettBebb]

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

[ERD · Accepted solution]

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

[ScarlettBebb]

@ERD Thank you so much that worked perfectly 🙂