Create new column with specific data from a different column

[Anonymous]

Hi everyone

I have a a table where i need a new column made based on info on one of the other columns.

The table looks like this 

Order No.	Type	Description
1	A	(blank)
1	B	(blank)
1	C	Next week delivery
2	A	(blank)
2	B	Check bounced
2	C	Return to sender

I want to create a new column that duplicates the Description of a Order No. where the Type is C to the rest of the rows of that Order. So in the end it should look like this:

Order No.	Type	Description	New column
1	A	(blank)	Next week delivery
1	B	(blank)	Next week delivery
1	C	Next week delivery	Next week delivery
2	A	(blank)	Return to sender
2	B	Check bounced	Return to sender
2	C	Return to sender	Return to sender

I achieved this already with a simple LOOKUPVALUE measure:

LOOKUPVALUE(Table[Description], Table[Type], "C", Table[Order No.], SELECTEDVALUE(Table[Order No.]))

But the calculation is too heavy for the amount of rows i have. That's why i want it done as a calculated column so the heavy lifting is done when the user opens the report.

[amitchandak · Accepted solution]

@Anonymous , Try new column

New column =
var _ord = [Order No]
var _type = "C"
return
if(isblank([Description]), maxx(filter(Table, [Order No] =_ord && [Type] ="C"),[Description]) ,[Description])

[Anonymous]

Thank you for the swift answer @amitchandak 

There was a little misunderstanding in how i wanted the column. Your calculation first checked for a description and then found the one with type "C" if the current Description was blank. I needed all to be from their respective type "C", so i altered your calculation and removed the IF sentence along with isblank.

Like this:

New column =
var _ord = [Order No]
var _type = "C"
return
maxx(filter(Table, [Order No] = _ord && [Type] = _type),[Description])

That did the trick and now it works like a charm. Thank you for the help.