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

The result is not correct, it seems like I got only the newly active assets in the period, not the accumulated active assets

  • AnthonyGenovese's avatar
    AnthonyGenovese
    Icon for Resolver III rankResolver III
    3 years ago

    I don't understand what you mean. With the details you already provided, what rows are being counted and what rows are being excluded?

    • brechtt's avatar
      brechtt
      Regular Visitor
      3 years ago

      I'm starting to understand what goes wrong. In my model I have a relationship between the In use column and the date column of my datetime tabel. So when I create a barchart with the data on the x-axis, the data is automatically filtered. I only get to see the number of cars where the In Use data is the same as the X-axis label. I don't want that, for every label on the x-axis, I want to visualize the number of cars that are active (meaning a-axis label is between In Use and Out Of Use)

      • AnthonyGenovese's avatar
        AnthonyGenovese
        Icon for Resolver III rankResolver III
        3 years ago

        What about something like

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