topic Re: COUNTIFS with Multiple Statements referring to Current Row value in DAX Commands and Tips

COUNTIFS with Multiple Statements referring to Current Row value

SteveMBSDO — Tue, 19 Dec 2023 13:32:55 GMT

For each row, I want to get a unique following number per Focus Tank. In Excel I accomplisted this by a COUNTIFS statement:

COUNTIFS if:

Column [Focus Tank] matches the [Focus Tank] value of the current row.

Column [NewStart] is less than or equal to the [NewStart] value of the current row.

Works a charm.

In PowerBI I came up with this, but it is not functioning because I don't know how to make code "VALUE(45907.74585)" dynamic. Meaning, this value should get the value from it's current ROW, and not be hardcoded. Anyone know what I'm missing here?

Thanks!

Re: COUNTIFS with Multiple Statements referring to Current Row value

Dangar332 — Tue, 19 Dec 2023 14:05:12 GMT

HI, SteveMBSDO

if it is measure then try below
measure =
var a = max(tablename[column name from value coming])

and compare it like (<a instead of values(...))

code like
measure =
var a = max(tablename[column name from value coming])

return

countx(

..........,

.....[datetime chrono]<a

)

if its column then
var a = tablename[column name from value coming]

Re: COUNTIFS with Multiple Statements referring to Current Row value

SteveMBSDO — Tue, 19 Dec 2023 14:06:48 GMT

Hi Dangar332 , sorry I forgot to mention it's a table.

Re: COUNTIFS with Multiple Statements referring to Current Row value

Dangar332 — Tue, 19 Dec 2023 14:22:35 GMT

Hi, SteveMBSDO

transaction = var a = 'transaction database'[datetime chrono] return countx( filter('transaction database', 'transaction database'[focus tank]=30 && 'transaction database'[datetime chrono]< a ), 'transaction database'[datetime chrono] )

Re: COUNTIFS with Multiple Statements referring to Current Row value

SteveMBSDO — Tue, 19 Dec 2023 14:31:57 GMT

Hi, Dangar332 Awesome! Thank you. I've also added the dynamic focus tank to the list of variables: