Create a column based on the value in same row or different row

[Anonymous]

I have below table, I want to create a new column that shows the default integration for the ID. i.e NewColumn below

If the there is Default_Flag = 1 for then use the Integration value from that row and fill the Newcolumn with that value in all rows for that ID

i.e for ID 001 first row has the default_Flag = 1 so will use value "A" from integrations column and fill in NewColumn for all the rows for 001 with "A"

ID 003 doesnt have any rows with default_Flag = 1 so will fill NewColumn as "No Default"

How do I do it?

ID	Integration	Default_Flag	NewColumn
001	A	1	A
001	C	0	A
001	B	0	A
002	B	1	B
002	D	0	B
003	A	0	No Default
003	B	0	No Default

Thanks

[TheoC]

Hi @Anonymous 

Here is a quick calcualted column that will give you what you're after!

Calculated Column = 

VAR _1 = MAXX ( FILTER ( 'Table' , 'Table'[ID] = EARLIER ('Table'[ID] ) && 'Table'[Default Flag] = "1" ) , 'Table'[Integration] )

RETURN

IF ( ISBLANK ( _1 ) , "No Default" , _1 )

Output is as per below:

Hope this helps!

Theo 🙂

[CNENFRNL · Accepted solution]

Excel worksheet formula is enough

[Greg_Deckler]

@Anonymous Not sure I quite follow the rules on how to arrive at the values.

[Anonymous]

Thank you for your response @Greg_Deckler 

I have updated the question, let me know if that helps