Forum Discussion
Create new column with specific data from a different column
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.
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])
2 Replies
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])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.