DISTINCTCOUNT for Year Ranges with Two Columns

[Mitig]

Greetings, 

Brand new to PBI here. I am trying to create a measure that counts "ID" column based off of "YEAR" column. HOWEVER, another column ("TERM") has different codes before 2016 than after. My company started changing codes that year based off of reporting changes.) Codes for pre-2016 are 5, 10, 20, and 30. Codes for 2016 and after are 20, 30, 35, and 40. (20 and 30 mean the same time period, so that does not need to change, but the difference in the other numbers indicates different time ranges.)

One small hitch: 2016 has both sets of codes associated with it -- but I only need to use 20, 30, 35 and 40 for the count within that year. 

In short, I'm trying to find a way to have a consistent, distinct count of "ID" by "YEAR", and then filtering by the proper "TERM" code depending on the year. Pre-2016 will use codes 5, 10, 20, and 30 for the distinct count; 2016 and after will use codes 20, 30, 35, and 40. 

------- Edited Here --------

Thank you kindly @Anonymous  and @az38 for the quick responses -- and below is a sample dataset and then a desired outcome. I color coded who would drop and who would stay in the count, and provided details below. Definitely let me know if I can clarify further. 

TABLE1

ID	YEAR	TERM
0001	2015	5
0002	2015	10
0003	2015	20
0004	2015	30
0005*	2016	5
0006*	2016	10
0007	2016	20
0008	2016	30
0009	2016	35
0010	2016	40
0011	2017	20
0012	2017	30
0013	2017	35
0014	2017	40
0014**	2017	30
0014**	2017	40
0001***	2017	40

The distinct count for each year would be as follows: 

2015 = 4 IDs. 

2016 = 4 IDs. (The two IDs indicated with a single asterisk (*) are dropped due to the phased out codes occurring in that year ("5" and "10"). These codes do not exist after 2016 and only need to be excluded from this 2016. 

2017 = 5 IDs. (The double asterisk (**) is dropped due to ID duplication within the same year. There are numerous duplicates in the actual dataset, so a distinct count would be needed here.) ***ID 0001 is counted since this person is present in a different year (2015), but it is not present within the same year. I failed to mention this in the original post. 

Thank you again for any help! 

------- END EDITS --------

All columns are from the same table name (TABLE1). 

Any help is so greatly appreciated! 

[az38 · Accepted solution]

@Mitig 

maybe you should try a measure like

Measure = CALCULATE(DISTINCTCOUNT(Table1[ID]), 
FILTER(ALL(Table1), Table1[YEAR]=SELECTEDVALUE(Table1[YEAR]) && (Table1[TERM]>10 || Table1[YEAR]<2016)))

[az38]

Hi @Mitig 

its unclear without data example, but maybe you should use ALLEXCEPT() function like

Measure = 
CALCULATE(COUNTROWS('Table'), ALLEXCEPT('Table', 'Table'[YEAR], 'Table'[TERM]) )

[Mitig]

Thank you - I did look into this one after your reply, but due to there being duplicated rows, counting the raw number of rows would yield an excessive number of IDs. (I did not mention there were duplicates in the original posting, so that is definiely my bad for lack of clarity.) As such, it has to be a distinct count of IDs. I also edited the original post with a sample dataset and how the count would come out. Cheers! 

[az38 · Accepted solution]

@Mitig 

maybe you should try a measure like

Measure = CALCULATE(DISTINCTCOUNT(Table1[ID]), 
FILTER(ALL(Table1), Table1[YEAR]=SELECTEDVALUE(Table1[YEAR]) && (Table1[TERM]>10 || Table1[YEAR]<2016)))

[Anonymous · Accepted solution]

Try this

Measure=
Var a=Calculate(distinctcount(table[id]),filter(table,table[year]<2016))
Var b=Calculate(distinctcount(table[id]),filter(table,table[year]>=2016 && table[code]<> 5 &&table[code]<> 10))

Return
If(max(year)<2015,a,b)

Thanks
Pravin

[Anonymous]

Please share sample data and expected output.

Thanks,

PRavin