SUM distinct values with multiple filters

[Anonymous]

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 rows in this image.

And preferably all in one measure

The total sum should be 2.54

Thanks

[jdbuchanan71 · Accepted solution]

@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"
)

[parry2k · Accepted solution]

@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.⚡

[Anonymous]

@parry2k That may be a solution, but there appears to be an error in your DAX after the RETURN statement

[parry2k]

@Anonymous fixed, it was missing a column name.

The issue I see with @jdbuchanan71 solution is that it is giving you maximum value across all the dates, but not the value of most recent date. may be that is not the requirement.

for example, if one feature id has two records 

Jan 01st 1000

Jan 02nd 800

the measure will return 1000 whereas the  latest date is Jan 02nd and not sure if you are looking for 800 or 1000 in this case

FYI, otherwise @jdbuchanan71 solution is better.

[jdbuchanan71]

@parry2k is correct, based on your sample I made the assumption that the km_length would be the same for the same feature_id regardless of date so MAX was safe.  If this is not the case you should look at his measures for your solution.

[Anonymous]

Yeah I managed to get @parry2k's working, am currently trying both solutions and looking for discrepancies between the two. Based on my original claim, I do think his answer is more correct and will accept his solution. Thank you both

[jdbuchanan71 · Accepted solution]

@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"
)