Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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])
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,
Ryan
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |