Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filter table to get rows based on distinct values in column

Hi all!

 

I had searched but do not seem to find a solution. Please help?

 

I have the following table (sample) and need a Measure to sum up the duration for  distinct IDs only i.e. (10 + 13 + 5 + 4 = 32 minutes). How do I do that?

 

ID           Duration (minutes)

101         10

101         10

130         13

130         13

130         13

142         5

165         4

165         4

 

Thanks in advance!

 

Regards,

Yee Pin

Need Help
  • AlB's avatar
    AlB
    7 years ago

    Hi Anonymous 

    Assuming duration is always the same for a specific ID:

    Measure =
SUMX ( DISTINCT ( Table1[ID] ), CALCULATE ( MIN ( Table1[Duration] ) ) )

     

3 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi Anonymous 

    Assuming duration is always the same for a specific ID:

    Measure =
SUMX ( DISTINCT ( Table1[ID] ), CALCULATE ( MIN ( Table1[Duration] ) ) )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      AI  Awesome! Thank you so much!!

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi,

        You can create a measure as below. This will work even if the values are different.

         

        total =
        VAR distinct_sum = SUMX( SUMMARIZE( Data, Data[ID], Data[Duration] ), Data[Duration] )
        RETURN
        distinct_sum