3 Replies Latest reply: Sep 13, 2018 10:24 AM by Polina Nosulko

# How to count the maximum value for each distinct ID?

I am trying to get a bar chart to show the number of times each maximum value occurs, per distinct ID. The maximum value can be an integer between 1 and 6. An example of data is below:

Episode IDMost recent form ID
1013
1012
1011
2502
2501
552
551
6384
6383
6382
6381

I am wanting to show a count of the maximum number in the 'most recent form ID' for each distinct episode ID. So, for example, the maximum value of the form ID for episode 101 is 3, so this counts as 1 episode ID. 2 is the maximum number for IDs 55 and 250, so this should count 2 distinct episode IDs. However, my current formula is currently counting every distinct episode ID that has a 2 (even if it is not the max value), so in this example it is returning a count of 4 (not 2).

My current formula is this:

Max(Aggr(Count({<[Opened in past 12 months?] = {'Yes'}>} distinct [Caf Episode ID]), [Most recent Form ID]))

And this produces the following graph:

I know this is wrong, because when I drill down into each column, the single episode ID that falls under '6' also appears in every other 'most recent form ID' group - but I only want it to appear in '6', because this is the maximum 'most recent form ID' for this distinct Episode ID...

Can anyone help? Thank you in advance!

• ###### Re: How to count the maximum value for each distinct ID?

May be try with a calculated dimension

Aggr(Max(ID), [Episode ID])

Expression

Count(DISTINCT [Episode ID])

• ###### Re: How to count the maximum value for each distinct ID?

Thank you Sunny, that worked! Most appreciated.

• ###### Re: How to count the maximum value for each distinct ID?

hi Sunny,

How would you fix this then?

First, in Straight table  to have the max ID per Episode ID:

101 - 3

250 - 2

55 - 2

638 - 4

and then in Bar chart to have the average in general for all Episode ID's?

Avg =  2.75

((3+2+2+4)/4)