cancel
Showing results for
Did you mean:  Super User

## Count Rows if

Hello i am trying to count rows between dates, it was wotking fine but when i add 'FACT - CW'[Transaction] = "test" as a filter it seems to ignore it. This is a calculated col

``````Days Old =
CALCULATE (
COUNTROWS ( 'DIM - Date Table' ),
DATESBETWEEN ( 'DIM - Date Table'[Date], 'FACT - CW'[Date of Receipt],'FACT - CW'[C-Date]-1),
'DIM - Date Table'[IsWorkingDay] = 1,
'DIM - Date Table'[IsHoliday] = 0,
'FACT - CW'[Transaction] = "test"
)``````

1 ACCEPTED SOLUTION  Super User

``````Days Old =
CALCULATE (
COUNTROWS ( 'DIM - Date Table' ),
DATESBETWEEN (
'DIM - Date Table'[Date],
'FACT - CW'[Date of Receipt],
'FACT - CW'[C-Date] - 1
),
'DIM - Date Table'[IsWorkingDay] = 1,
'DIM - Date Table'[IsHoliday] = 0,
'FACT - CW'[Transaction] = "test",
CROSSFILTER ( 'DIM - Date Table'[Date], 'FACT - CW'[Date], BOTH )
)``````
4 REPLIES 4  Super User

``````Days Old =
CALCULATE (
COUNTROWS ( 'DIM - Date Table' ),
DATESBETWEEN (
'DIM - Date Table'[Date],
'FACT - CW'[Date of Receipt],
'FACT - CW'[C-Date] - 1
),
'DIM - Date Table'[IsWorkingDay] = 1,
'DIM - Date Table'[IsHoliday] = 0,
'FACT - CW'[Transaction] = "test",
CROSSFILTER ( 'DIM - Date Table'[Date], 'FACT - CW'[Date], BOTH )
)``````  Solution Sage

Your date table filters FACT - CW - not the other way around, that's why it's ignored.

Can you share a picture of the data model (relations)?  Super User

this as close as i can think (Dax has changed a bit), it does not work

``````Days Old =
CALCULATE (
COUNTROWS ( 'DIM - Date Table' ),
DATESBETWEEN (
'DIM - Date Table'[Date],
'FACT - Casework'[Date of Receipt],
'FACT - Casework'[COO Date] - 1
),
'DIM - Date Table'[IsWorkingDay] = 1
&& 'DIM - Date Table'[IsHoliday] = 0,
FILTER ( 'FACT - Casework', 'FACT - Casework'[Transaction] = "unallocated" )
)``````  Super User

The only relationship is between the date tabel and the FACT CW table  