topic Re: Count rows in a fact table based on a column and a column in a dat table in DAX Commands and Tips

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

Ojesim — Wed, 19 Mar 2025 22:17:03 GMT

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

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

Deku — Thu, 20 Mar 2025 00:23:04 GMT

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

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

lbendlin — Thu, 20 Mar 2025 00:20:35 GMT

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

a date table needs a date column.

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

Ojesim — Thu, 20 Mar 2025 06:51:18 GMT

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

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

Ojesim — Thu, 20 Mar 2025 06:54:01 GMT

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

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

Deku — Thu, 20 Mar 2025 07:27:57 GMT

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

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

Ojesim — Thu, 20 Mar 2025 17:06:06 GMT

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

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

Ojesim — Thu, 20 Mar 2025 17:39:24 GMT

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

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

Deku — Thu, 20 Mar 2025 21:31:49 GMT

Not sure your first images has uploaded correctly