Forum Discussion
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/2021 | 04/08/2030 |
I have a table with cars and a date table. I want to create a measure that I can use to draw a Column chart. On the X-axis we have the quarters, on the y-axis the total numbers of cars that are in use on the last day of the quarter. This is what I have but it does not seem to work correctly. I'm struggling with the today()
| ||
| Active assets = CALCULATE( COUNTROWS('Fleet'), 'Fleet'[In use ] <= today() && 'Fleet'[Out of use] >= today() ) |
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
11 Replies
AnthonyGenoveseResolver III
What isn't working about it? Can you provide more details?
The result is not correct, it seems like I got only the newly active assets in the period, not the accumulated active assets
- AnthonyGenovese
I don't understand what you mean. With the details you already provided, what rows are being counted and what rows are being excluded?
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)