Forum Discussion

brechtt's avatar
brechtt
Regular Visitor
3 years ago
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...
  • brechtt's avatar
    brechtt
    3 years ago

    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
brechtt's avatar
brechtt
Regular Visitor
3 years ago

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's avatar
AnthonyGenovese
Icon for Resolver III rankResolver III
3 years ago

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!