Need help with DAX to filter and count

[RJMonteza]

I have a data of 3 years with 12 Periods each. I need to count the redlights categorized by YES/NO per year and period:

I have my initial DAX: 

1. CALCULATE(COUNTA(REDLIGHT[Start Time]),REDLIGHT[VALID OE ERROR (Y/N?)] IN {"YES"})

2. SUMX(

    VALUES(REDLIGHT[Start Time]),

    CALCULATE(

        COUNTROWS('REDLIGHT'),

        REDLIGHT[VALID OE ERROR (Y/N?)] IN {"YES"}

However, both DAX count the valid redlights ("YES") to all years though I stated to count per date.

Thanks!

[Greg_Deckler]

@RJMonteza Maybe:

Measure = COUNTROWS( FILTER( 'REDLIGHT', [VALID OE ERROR (Y/N?)] = "YES" ) ) 

[RJMonteza]

Hello Greg,

I also need to filter the date and year. I need to count how many YES in a perod in this year.

Thanks!

[Greg_Deckler]

@RJMonteza I assumed those would just be part of the visual.

[RJMonteza]

Nope, I need to count all the valid redlights (YES) per month of the year. Right now, my DAX is calculating all the valid redlights for those 3 years. Example: Sum of 2021, 2022, and 2023 of PD1 = RL count. Which is not correct, I need it to look like this up to perod 12:

2021	2022	2023
PD 1 = 2	PD 1 = 0	PD 1 = 1
PD 2 = 1	PD 2 = 2	PD 2 = 0
PD 3 = 0	PD 3 = 1	PD 3 = 1

[Greg_Deckler]

@RJMonteza Not sure I am completely following but you can do this:

Measure 2021 = COUNTROWS( FILTER( 'REDLIGHT', [VALID OE ERROR (Y/N?)] = "YES" && [YEAR] = "2021" ) ) 

[RJMonteza]

Hmm... Can we have it apply to all years instead of filtering just 1 year, then per period, then countrows. I am seeing that there is a need to use VAR, but I'm not sure how..

[Greg_Deckler]

@RJMonteza OK, so like this?

Measure 2021 = 
  VAR __Table = 
    SUMMARIZE(
      FILTER( 'REDLIGHT', [VALID OE ERROR (Y/N?)] = "YES" ),
      [YEAR},
      [PERIOD],
      "__Count", COUNTROWS( 'REDLIGHT' )
    )
  VAR __Result = SUMX( __Table, [__Count] )
RETURN
  __Result

Note that sample data and expected result would make this FAAARRRR more easy to figure out what you are going for here.

[RJMonteza]

I'm having this as a result:

The result I need is a a single digit one up to PD 8 only, since we only have a data until PD 8. I need this to be:

PD 1 = 1

PD 2 = 1

PD 3 = 0

PD 4 = 1
PD 5 = 1

etc.

Note: The redlight table is another table from this one.