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

     



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?

    • v-juanli-msft's avatar
      v-juanli-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      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.