Associate a max value with the original record

[Anonymous]

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_CODE	SEQN
1897	AHA-3YR	351319
1897	AHA-3YR	744415
1897	AHA-3YR	1132870
1914	AHA-2YR	266891
1914	AHA-3YR	488929
1914	AHA-3YR	878426
2038	AHA-1YR	190960
2038	AHA-1YR	638027
2038	AHA-1YR	857991
2038	AHA-2YR	259786
2038	AHA-2YR	475620
2038	AHA-3YR	990669
2557	AHA-1YR	190713
2557	AHA-1YR	255686
2557	AHA-1YR	333541
2557	AHA-3YR	989648
3555	AHA-1YR	279065
3555	AHA-1YR	415330
3555	AHA-1YR	537121
3555	AHA-1YR	656033
3555	AHA-1YR	1112955
3555	AHA-2YR	799658

The latest (MAX) SEQN value determines the current PRODUCT_CODE of the ID. I found some good forum postings on returning a Max value based on another column. So wrote the following DAX statement to create a measure called LatestSEQN:

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

This get's me the following: 

ID	PRODUCT_CODE	SEQN	LatestSEQN
1897	AHA-3YR	351319	1132870
1897	AHA-3YR	744415	1132870
1897	AHA-3YR	1132870	1132870
1914	AHA-2YR	266891	878426
1914	AHA-3YR	488929	878426
1914	AHA-3YR	878426	878426
2038	AHA-1YR	190960	990669
2038	AHA-1YR	638027	990669
2038	AHA-1YR	857991	990669
2038	AHA-2YR	259786	990669
2038	AHA-2YR	475620	990669
2038	AHA-3YR	990669	990669
2557	AHA-1YR	190713	989648
2557	AHA-1YR	255686	989648
2557	AHA-1YR	333541	989648
2557	AHA-3YR	989648	989648
3555	AHA-1YR	279065	1112955
3555	AHA-1YR	415330	1112955
3555	AHA-1YR	537121	1112955
3555	AHA-1YR	656033	1112955
3555	AHA-1YR	1112955	1112955
3555	AHA-2YR	799658	1112955

But I realized that this just creates a value, and there is no relationship between LatestSEQN, PRODUCT_CODE, ID. Wondering if I'm approaching this wrong? My desired output is:

ID	PRODUCT_CODE	LatestSEQN
1897	AHA-3YR	1132870
1914	AHA-3YR	878426
2038	AHA-3YR	990669
2557	AHA-3YR	989648
3555	AHA-1YR	1112955

So i can do a count of ID on PRODUCT_CODE to get:

PRODUCT_CODE	COUNT
AHA-1YR	1
AHA-3YR	4

thank you in advance!

[v-xuding-msft · Accepted solution]

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])

[Anonymous]

Many thanks Xue, you nailed it!

Quick question if you have time. What is the difference infunctions for Count 1 and Count 2? Would I use under different conditions? When I tried on the larger data set and filtering on other conditions I get different counts. Count 2 appears to be the accurate count I need, although still validating this. 

Cheers,
Ryan

[v-xuding-msft]

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]

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.