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

Then something like

 

var dateFilter=selectedvalue(dateTable[date])

var result= CALCULATE( COUNTROWS('Fleet'), ALL(DateTable), 'Fleet'[In use ] <= dateFilter && 'Fleet'[Out of use] >= dateFilter)​

return result 

brechtt's avatar
brechtt
Regular Visitor
3 years ago

This doesn't return anything at all

 

  • 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

    You 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. 

  • 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!