Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Associate a max value with the original record

I have a table called "Activity" that I'd like to count IDs by PRODUCT_CODE where the max SEQN record is selected for a given ID. A sample of the data from the Activity table.    ID PRODUCT...
Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi Anonymous ,

     

    You could try like this:

    LatestSEQN =
VAR CurrentID =
    SELECTEDVALUE ( 'Activity'[ID] )
VAR MAX_SEQ =
    MAXX (
        FILTER ( ALL ( 'Activity' ), Activity[ID] = CurrentID ),
        'Activity'[MaxSeq]
    )
RETURN
    IF ( MAX ( Activity[MaxSeq] ) = MAX_SEQ, MAX_SEQ, BLANK () )

    Count 1 =
CALCULATE (
    DISTINCTCOUNT ( Activity[ID] ),
    FILTER (
        ALLEXCEPT ( Activity, Activity[PRODUCT_CODE] ),
        Activity[MaxSeq] = [LatestSEQN]
    )
)


Count 2 = COUNTX(Activity,[LatestSEQN])

     

v-xuding-msft's avatar
v-xuding-msft
Community Support
6 years ago

Hi Anonymous ,

 

For "Count 1", I calculate based on ID. And for "Count 2", it counts the measure "LatestSEQN".  I write two formulas because I don't know what your actual data like. If "Count 2" works for you now, you don't need to add other conditions.

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

That's what I thought, but wanted to make sure I understood your logic correctly. Yes, I do want to count only "LatestSEQN". Thank you for clarifying.