Forum Discussion
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_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!
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])
4 Replies
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])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,
RyanHi 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.