Forum Discussion
SUM distinct values with multiple filters
I have a table with the following columns My goal is to SUM the km_length for the unique feature_id where the last end_date of new_state is "UNVERIFIED", which would be all of the highlighted ...
Anonymous see if something like this will work for you.
Measure = CALCULATE ( SUMX ( VALUES ( measurements[feature_id] ), CALCULATE ( MAX ( measurements[km_length] ) ) ), measurements[new_state] = "UNVERIFIED" )Anonymous I like to break measures and here is what you can do
Max Value = VAR __filter = FILTER( ALLEXCEPT ( Table, Table[Feature_Id ), Table[New State] = "Unverified" ) VAR __maxDate = CALCULATE ( MAX ( Table[Date] ), __filter ) RETURN CALCULATE ( MAX ( Table[Value] ), __filter Table[Date] = __maxDate ) Sum of Max value = SUMX ( VALUES ( Table[Feature_Id] ), [Max Value] )I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
jdbuchanan71
Super User
Super UserAnonymous see if something like this will work for you.
Measure =
CALCULATE (
SUMX (
VALUES ( measurements[feature_id] ),
CALCULATE ( MAX ( measurements[km_length] ) )
),
measurements[new_state] = "UNVERIFIED"
)