Forum Discussion

JoeCrozier's avatar
JoeCrozier
Helper II
2 years ago
Solved

Create True/False variable based on other row grouping

This might be pretty similar to this one: https://community.fabric.microsoft.com/t5/Desktop/Determine-if-a-value-exists-in-one-column-when-grouping-by/td-p/2187917   but I have data like this in Po...
Need Help
ryan_mayu's avatar
ryan_mayu
Super User
2 years ago

JoeCrozier 

 if there are only 2 values in IRB you can try this

Column = if( CALCULATE(DISTINCTCOUNT('Table'[IRB Committee]),ALLEXCEPT('Table','Table'[Protocol]))=2,TRUE(),FALSE())
 
if you will have other  data in IRB, you need to try this
 
Column 2 = if('Table'[IRB Committee]="CIRB"&&maxx(FILTER('Table','Table'[Protocol]=EARLIER('Table'[Protocol])&&'Table'[IRB Committee]="LIRB"),'Table'[IRB Committee])<>"" || 'Table'[IRB Committee]="LIRB"&&maxx(FILTER('Table','Table'[Protocol]=EARLIER('Table'[Protocol])&&'Table'[IRB Committee]="CIRB"),'Table'[IRB Committee])<>"",TRUE(),FALSE())
 
  • JoeCrozier's avatar
    JoeCrozier
    Helper II
    2 years ago

    ryan_mayu  Thank you, I believe that will probably work.  There are only two options in that column (well, there are also blanks, will that be a problem you think?).  However I'm having trouble implementing it.  In this picture you'll see the actual column names (slightly diffent than what I gave you).   And I'm assuming in your code where it said 'table' that should be the name of the table?  If so its LIRB Approval and you can see that there.

    The problem is it says its expecting a token ')' and I cant see where that would fit:

     

    Any ideas?  Am I missing something obvious?

    • ryan_mayu's avatar
      ryan_mayu
      Super User
      2 years ago

      not creating a column in PQ. you need to close the PQ window and use DAX to create a column

      • JoeCrozier's avatar
        JoeCrozier
        Helper II
        2 years ago

        Will it still then be available later on in Power query?  I need to do later power query steps based on that column being available.