Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

 

And preferably all in one measure

The total sum should be 2.54

 

Thanks

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    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's avatar
    parry2k
    6 years ago

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

     

6 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        6 years ago

        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.