Forum Discussion

NiugeS's avatar
NiugeS
Icon for Helper V rankHelper V
6 years ago
Solved

Help 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 g...
Need Help
  • Fowmy's avatar
    Fowmy
    6 years ago

    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

     



Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
NiugeS
Icon for Helper V rankHelper V
6 years ago

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's avatar
    Fowmy
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      NiugeS
      Icon for Helper V rankHelper V
      6 years ago

      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"
      )
      • Fowmy's avatar
        Fowmy
        Icon for Super User rankSuper User
        6 years ago

        NiugeS 

         

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

        can you verify and confirm?