Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Ojesim
Regular Visitor

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

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 

1 ACCEPTED SOLUTION
Deku
Super User
Super User

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


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

View solution in original post

8 REPLIES 8
lbendlin
Super User
Super User

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

a date table needs a date column.

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

Deku
Super User
Super User

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


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!
Ojesim
Regular Visitor

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

Ojesim
Regular Visitor

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

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


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!
Ojesim
Regular Visitor

Screenshot 2025-03-20 170015.pngScreenshot 2025-03-20 170056.png

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

Not sure your first images has uploaded correctly 


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.