Forum Discussion
Helper VHelp with Query - Countif Valid / Not Valid
Hi,
I have 3 columns. 1st column is a group that consists of people who have a category of valid or not valid. I am trying to work out how I can make an additional column that will say if each group has atleast 1 valid member.
| Group | Person | Valid | Group Has Valid Member |
| Group 1 | Person 1 | Valid | Yes |
| Group 1 | Person 2 | Not Valid | Yes |
| Group 2 | Person 3 | Valid | Yes |
| Group 2 | Person 4 | Valid | Yes |
| Group 3 | Person 5 | Not Valid | No |
| Group 4 | Person 6 | Not Valid | No |
| Group 5 | Person 7 | Valid | Yes |
| Group 5 | Person 8 | Not Valid | Yes |
| Group 6 | Person 9 | Not Valid | No |
I'm not sure what to look for so any guidance greatly appreciated.
Many thanks
NiugeS
Add the following Column to your table:Valid Member = IF( CALCULATE( COUNTROWS('Table'), ALLEXCEPT('Table','Table'[Group]), 'Table'[Valid] = "Valid" ) > 0 , "Yes", "No" )________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
7 Replies
- Fowmy
Super User
NiugeS
Add the following Column to your table:Valid Member = IF( CALCULATE( COUNTROWS('Table'), ALLEXCEPT('Table','Table'[Group]), 'Table'[Valid] = "Valid" ) > 0 , "Yes", "No" )________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
- Greg_Deckler
Community Champion
NiugeS - You could create a measure like this:
Has Valid Member = VAR __Group = MAX('Table'[Group]) VAR __Table = FILTER('Table',[Group] = __Group && [Valid] = "Valid") RETURN IF(ISBLANK(__Table),"No","Yes")As a column the same thing would be:
Has Valid Member = VAR __Group = 'Table'[Group] VAR __Table = FILTER('Table',[Group] = __Group && [Valid] = "Valid") RETURN IF(ISBLANK(__Table),"No","Yes")
NiugeSFowmy Greg_Deckler Thank you both for taking the time to respond. It appears I may have needed to provide you with more information.
The table is made up of two sources which has a lot of columns in both. Is there a way to get a simmilar result without merging the two sources?
Source 1 has Group and person and Source 2 has person and valid.
To avoid any confusion, i've attached a test pbix file. Is it possible to create a calculated column or measure in this scenario? Any help appreciated.
Thank you
