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

finding if a value appears at least once in a column filtered by another column

I need help finding if a value appears at least once in a column filtered by another column.

Following the simplified Table, I need to check if the ID has been registered at least once for the class.

 

IDClassRegistered to Class 1111?
AAAA1111Yes
BBBB2222No
BBBB3333No
BBBB4444No
CCCC1111Yes
CCCC4444Yes
DDDD3333No
EEEE1111

Yes

EEEE2222

Yes

 

My school of thought was to filter each ID and check wheter or not one of the class where the specified class, a but ended up getting nowhere. I can do it if I create a new table, but I need it to bein a column of the same table.

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi, @GabrielKoch 

Please check the below for creating a new column.

 

Picture2.png

 

Registered to Class 1111? =
VAR currentid = 'Table'[ID]
VAR alltablefilteredbycurrentid =
SUMMARIZE ( FILTER ( 'Table', 'Table'[ID] = currentid ), 'Table'[Class] )
RETURN
IF ( { 1111 } IN alltablefilteredbycurrentid, "Yes", "No" )
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

2 REPLIES 2
fhill
Resident Rockstar
Resident Rockstar

How's this custom column?

VAR an Array of 'Good' IDs where Class = 1111   (Or whatever you are seraching for...)

Then if current row's ID is 'Contained' in the Array, YES, else NO

 

fhill_0-1619717021990.png

 

 



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


I’ll be speaking at #MySummitNA this year.
Check out all the sessions, including mine here: https://hubs.ly/H0SXmgH0
I hope to see you in Houston October 12-15 #MySummitNA

Proud to be a Super User helping give back to the community!
Thank You!




Jihwan_Kim
Super User
Super User

Hi, @GabrielKoch 

Please check the below for creating a new column.

 

Picture2.png

 

Registered to Class 1111? =
VAR currentid = 'Table'[ID]
VAR alltablefilteredbycurrentid =
SUMMARIZE ( FILTER ( 'Table', 'Table'[ID] = currentid ), 'Table'[Class] )
RETURN
IF ( { 1111 } IN alltablefilteredbycurrentid, "Yes", "No" )
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

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