Count rows in a fact table based on a column and a column in a dat table

[Ojesim]

have an IT helpdesk table that has one of its colums as Status(closed as ID 1 , open as ID 2, Inprogress as ID 3).

I created a date table with Year(2020, 2021, 2022, 2023), Qtr and month columns.

I want to calculate how many Closed tickets we have based on the ticket status and year 2023.

I tried CALCULATE(COUNT(table[status]), FILTER (Table, Table[status]] = 1 && Date[Year] = 3 but it's not working. 

My model looks okay, it's all 1 to many relationships. 

Someone pls help me and also explain the concept as well. 

Thank you 

[Deku · Accepted solution]

CALCULATE(

COUNTROWS (table),

Table[status] = 1,

Date[Year] = 2023

)

This assumes the date dimension is joined to the main table on ticket's close date/month. Otherwise will need a inactive relation to close date, and use USERELATIONSHIP to activate it in the measure

[Ojesim]

Thank you, when I do this, it doesn't work, says my date table is not an actual table

[Deku]

Can we see some examples data to get a better idea of how to help

[Ojesim]

These are screenshots of some columns in the data, not sure what i am doing wrong please. Thank you

[Deku]

Not sure your first images has uploaded correctly 

[Ojesim]

I found where the error is. Your solution is correct  thanks for your help. 

[lbendlin]

I created a date table with Year(2020, 2021, 2022, 2023), Qtr and month columns.

a date table needs a date column.

[Ojesim]

It has a date column which is connected to the date column on the Fact table