Forum Discussion
Solved
count row on condition
I'm having some difficulties with a DAX query. I have this table: Name (string) In use (date) Out of use (date) Car 1 01/01/2020 06/05/2021 Car 2 01/06/2020 null Car 3 05/09/202...
this solved it:
Active assets = var endDate = MAX(DateTime[Date]) var result = CALCULATE( COUNTROWS('fleet'), REMOVEFILTERS('DateTime'), 'fleet'[in use] <= endDate, 'fleet'[out of use] > endDate || ISBLANK('fleet'[out of use]) ) return result
AnthonyGenovese
Resolver III
Resolver IIIYou will need to provide more concrete details then, showing relationships, table metadata, table data, the chart you are using, and how it is configured. The last code I sent you takes a look at the xaxis, then counts the rows for the fleet table by first removing the xaxis filter, but then applying it to the two columns.
this solved it:
Active assets =
var endDate = MAX(DateTime[Date])
var result =
CALCULATE(
COUNTROWS('fleet'),
REMOVEFILTERS('DateTime'),
'fleet'[in use] <= endDate,
'fleet'[out of use] > endDate
||
ISBLANK('fleet'[out of use])
)
return result- AnthonyGenovese
Nice. That is really close to what I wrote (good on you for accounting for blank), I wonder why yours worked and mine didn't!