Forum Discussion
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 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!
i am not good at M, here is a workaround for you
9 Replies
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 thisColumn 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())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?
not creating a column in PQ. you need to close the PQ window and use DAX to create a column
