Using the output of except - Finding what's there or not

[JackWren]

Hi, 

I am trying to use a switch function to classify my IDs based on their reccurence in my table. 

If the ID was present in 2021 and 2020, it's a "new 2021"

If it wasn't it is a "real new 2021"

If the ID was present in 2020 and 2019 it's a "new 2020"

If it wasn't it is a "real new 2020"

I have a table with my IDs and my starting date and it is linked to my calendar table which has current year flags (2021 = 0; 2020 = -1, 2019 = -2) 

Here is my calculated column but I have issues with the 4th line of code "New 2020" which does not return anything.

I tried with intersect instead because the idea was to see which ID were present in both compared years for the ones which are "new 2021 and 2020" but to no avail. 

CategoryCurrentYear =

Var TheseID = CALCULATETABLE(values('Table'[ID]),FILTER('DWH Calender','Calender'[CURRENT_YEAR_INDICATOR]=0))

Var TheseIDLastYear = CALCULATETABLE(values(' Table'[ID]),FILTER('DWH Calender','DWH Calender'[CURRENT_YEAR_INDICATOR]=-1))

Var TheseIDTheYearBeforelastYear = CALCULATETABLE(values('Table'[ID]),FILTER('DWH Calender','DWH Calender'[CURRENT_YEAR_INDICATOR]=-2))

RETURN

Switch(

True(),

COUNTROWS(EXCEPT(TheseID,TheseIDLastYear))=1,"RealNew2021",

COUNTROWS(EXCEPT(TheseIDLastYear,TheseIDTheYearBeforelastYear))=1,"RealNew2020",

COUNTROWS(EXCEPT(TheseID,TheseIDLastYear))=0,"New2021",

COUNTROWS(EXCEPT(TheseIDLastYear,TheseIDTheYearBeforelastYear))=0,"New2020",

blank()

)

Thanks

Jack

[Greg_Deckler]

@JackWren Sample data would help but I *think* countrows of blank is going to be blank, not 0 so that might be the problem?

Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[JackWren]

Sorry, 

Here is the sample data 

Date	ID
01/02/2021	AAA
16/03/2020	AAA
01/12/2020	BB
20/04/2015	BB
08/10/2021	CC
14/07/2019	DD
12/05/2020	DD

Results

AAA	New 2021
BB	Real New 2020
CC	Real New 2021
DD	New 2020