Forum Discussion

SteveMBSDO's avatar
SteveMBSDO
Icon for Helper I rankHelper I
2 years ago
Solved

COUNTIFS with Multiple Statements referring to Current Row value

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!

 

CountIfs
  • Dangar332's avatar
    Dangar332
    2 years ago

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

4 Replies

  • Dangar332's avatar
    Dangar332
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    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]

      • Dangar332's avatar
        Dangar332
        Icon for Resident Rockstar rankResident Rockstar
        2 years ago

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