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

 

 

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

4 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    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

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

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