topic Re: count row on condition in DAX Commands and Tips

count row on condition

brechtt — Thu, 20 Jul 2023 18:06:53 GMT

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() )

Re: count row on condition

AnthonyGenovese — Thu, 20 Jul 2023 18:21:00 GMT

What isn't working about it? Can you provide more details?

Re: count row on condition

brechtt — Thu, 20 Jul 2023 18:22:52 GMT

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

Re: count row on condition

AnthonyGenovese — Thu, 20 Jul 2023 18:24:38 GMT

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

Re: count row on condition

brechtt — Thu, 20 Jul 2023 20:14:40 GMT

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)

Re: count row on condition

AnthonyGenovese — Thu, 20 Jul 2023 20:17:36 GMT

What about something like

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

Re: count row on condition

brechtt — Thu, 20 Jul 2023 20:25:36 GMT

Now I have a constant value over the entire timerange, I suppose it's not filtering at all anymore. I don't understand the today(). It shouldn't be today(), it should be the month, year, ... of the x-axis

Re: count row on condition

AnthonyGenovese — Thu, 20 Jul 2023 20:28:32 GMT

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

Re: count row on condition

brechtt — Thu, 20 Jul 2023 20:40:20 GMT

This doesn't return anything at all

Re: count row on condition

AnthonyGenovese — Thu, 20 Jul 2023 20:45:42 GMT

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.

Re: count row on condition

brechtt — Wed, 26 Jul 2023 08:38:11 GMT

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

Re: count row on condition

AnthonyGenovese — Wed, 26 Jul 2023 21:13:02 GMT

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!