Count only once in a group

[Anonymous]

Hello all,

Can you please suggest how I can acheive the below requirement.

My data set is like below. It has 'ID' and 'Years' fields. One ID may be present in one or more 'Years' group but I want to count 'ID' only in the first group its present. The group order is  '0-2','3-5' and '6-10'.

For example ID 1 is present in '0-2' and '3-5' group but it should only be counted in first group i.e '0-2'. 

ID	Years
1	0-2
2	0-2
3	0-2
4	0-2
1	3-5
2	3-5
5	3-5
6	3-5
4	6-10
5	6-10
7	6-10

Below is the result I am looking for:  

Result
Years	Count
0-2	4
3-5	2
6-10	1

Let me know if I am not clear. Thank you.

[Stachu]

so you only want to count first occurence of the particular ID as sorted per Years?

[Anonymous]

@Stachu yes please. 

[v-lili6-msft]

hi, @Anonymous

If you want to filter the result, you could just improve the formula of  parry2k 's solution.

First, you must add a rank column for Years column.

Rank = RANKX( table1, Table1[Years], , ASC, Dense)

Second, Try this measure

Number of Ids = 
VAR t = CALCULATETABLE( VALUES( Table1[ID] ), FILTER( ALLSELECTED(Table1), Table1[Rank] < MAX( Table1[Rank] ) ) )
RETURN
CALCULATE( COUNT( Table1[ID] ),EXCEPT( VALUES( Table1[ID] ), t ) )

Result:

Best Regards,

Lin

[parry2k]

@Anonymous did you tried the solution I provided? Any issue ?

[Anonymous]

Hi @parry2k

I tried the method that you suggested with full dataset but in some instances I am getting different counts. Any chance I can send dataset to you?

Thank you

[parry2k]

@Anonymous sure we can look into this, share the dataset using onedrive/googledrive/dropbox etc and also point out in what case it doesnt work.

[Anonymous]

Hi, 

Here is the code for your issue.

let
Source = Excel.CurrentWorkbook(){[Name="Table14"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Years", type text}}),
#"Removed Duplicates" = Table.Distinct(#"Changed Type", {"ID"}),
#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Years"}, {{"Count", each Table.RowCount(_), type number}})
in
#"Grouped Rows"

Let me know, if you have any issue.

[parry2k]

@Anonymous your request is not clear, which values in 3-5 and 6-10 are getting counted?

[Anonymous]

Hi @parry2k,

In '3-5 Years' group ID's 5 and 6 has to be counted because 1 and 2 are already counted in 'Years 0-2' group.

And in 'Years 6-10' group ID, only 7 has to be counted as 4 is already counted in 'Years 0-2' group and 5 is counted in '3-5' group. 

Please let me know if I am not clear. 

[parry2k]

@Anonymous got it, try this

first add a column to assign id to each year group, I used Rank for this

Rank = RANKX( table1, Table1[Years], , ASC, Dense)

now add a measures to count IDs

Number of Ids = 
VAR t = CALCULATETABLE( VALUES( Table1[ID] ), FILTER( ALL(TAble1), Table1[Rank] < MAX( Table1[Rank] ) ) )
RETURN
CALCULATE( COUNT( Table1[ID] ),EXCEPT( VALUES( Table1[ID] ), t ) )

drop a table visual and add years and "number of ids" measure to this table, and you will get the result.