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

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors