Create True/False variable based on other row grouping

[JoeCrozier]

This might be pretty similar to this one: https://community.fabric.microsoft.com/t5/Desktop/Determine-if-a-value-exists-in-one-column-when-gro...

but I have data like this in Power Query:

Protocol	IRB Committee
Advil	CIRB
Advil	CIRB
CancerDrug	LIRB
LS-P-Joe	CIRB
LS-P-Joe	LIRB
Ski	CIRB
Ski	LIRB
Test	CIRB
Youtube	LIRB
Youtube	LIRB
Youtube	LIRB

I need to create a new variable that is 'TRUE' if both LIRB and CIRB exist for a given protocol, but false if only one of them exists for the protocol.

So for example, Ski has two rows, one for LIRB and one for CIRB, so ski is true.  

Youtube has only LIRB, so its false.

And CancerDrug only has one row, so its only got one of the two, so its false.

My ideal output would look like this:

Protocol	IRB Committee	Both
Advil	CIRB	FALSE
Advil	CIRB	FALSE
CancerDrug	LIRB	FALSE
LS-P-Joe	CIRB	TRUE
LS-P-Joe	LIRB	TRUE
Ski	CIRB	TRUE
Ski	LIRB	TRUE
Test	CIRB	FALSE
Youtube	LIRB	FALSE
Youtube	LIRB	FALSE
Youtube	LIRB	FALSE

I also have other columns not shown (action_date, submit_date, etc..), and need to do more transformations down the road, so hopefully however I accomplish this doesnt drop any columns I'll need.  Any help would be appreciated!

[ryan_mayu]

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

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

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

[JoeCrozier]

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.

[ryan_mayu · Accepted solution]

@JoeCrozier 

 i am not good at M, here is a workaround for you

[JoeCrozier]

This seems to work great!  To make sure i understand it and can implement, seems like you: copied the query, in the new copy you grouped by protocol and counted, and then you merged that count back into the original query, and created a true/false column for if the count was 2?

[ryan_mayu]

yes, any other issues you encountered?

[JoeCrozier]

Nope, worked great!

[ryan_mayu]

ok, glad to hear that