cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SuperCal99
Frequent Visitor

Finding Ids across multiple columns

Hi,

 

I have been asked to compare if a specific id is present across a number of years - yes and no means they are present, blank means they are not present.

Below is an example of what i need - the data is within one table

 

Essentially i need the result in column e, stating whether the ID is present within each year column "yes", "no", and ignoring blanks.

 

Any help or additional questions needed please let me know

 

Kind regards,

 

Callum

 

 

    Result 
Abcde 
ID201920202021in 2021 and 2020 and 2019? 
000113 NoYesYes 
000114  YesNo 
000115  YesNo 
000116No YesYes 
000117  YesNo 
1 REPLY 1
Tanushree_Kapse
Continued Contributor
Continued Contributor

Hi @SuperCal99 ,
You can create a calculated column using the IF condition as mentioned below:
e = IF(AND(AND(b=" ",c="No"),d="Yes"),"Yes",
      IF(AND(AND(b=" ",c=" "),d="Yes"),"No",
      IF(AND(AND(b=" ",c=" "),d="Yes"),"No",
      IF(AND(AND(b="No ",c=" "),d="Yes"),"Yes",
      IF(AND(AND(b=" ",c="Yes"),d="No"),"No",blank())))))

I hope this helps!

@SuperCal99 

Did I answer your question? Mark my post as a solution! 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.