Count Rows if

[NewbieJono]

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"
)

[tamerj1 · Accepted solution]

Hi @NewbieJono 

please try

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 )
)

[lukiz84]

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)?

[NewbieJono]

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" )
)

[NewbieJono]

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