Help with Query - Countif Valid / Not Valid

[NiugeS]

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

[Greg_Deckler]

@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")

[NiugeS]

@Fowmy @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.

Test PBIX File 

Thank you

[Fowmy]

@NiugeS 

The Calculated column I provided should still work, can you check,

________________________

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 🙂

YouTube, LinkedInplease.

  

[NiugeS]

@Fowmy   Thank you.  I got errors when I first tried but have since modified the calculated column you provided and modified it.  I'm not confident the below is correct but appears to work.  Wasn't sure which table to refer to.

Valid Member =

IF(

CALCULATE(

COUNTROWS('Table (2)'),

ALLEXCEPT('Table','Table'[Group]),

'Table (2)'[Valid] = "Valid"

) > 0 ,

"Yes",

"No"

)

[v-juanli-msft]

Hi @NiugeS 

Please add it to your "Table" table, as tested and analyzed, it shows a correct result and will get a correct result based on a bigger data.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy]

@NiugeS 

It looks fine, you have relationship between the tables and the results are correct as mentioned. 

can you verify and confirm?

[Fowmy · Accepted solution]

@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 🙂

YouTube, LinkedIn