Forum Discussion

NewbieJono's avatar
NewbieJono
Post Partisan
3 years ago
Solved

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 = C...
Need Help
  • tamerj1's avatar
    tamerj1
    3 years ago

    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's avatar
lukiz84
Memorable Member
3 years ago

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's avatar
NewbieJono
Post Partisan
3 years ago

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