Will the custom column values will change with updated values from data source

[ST_OK]

Hi All,

I 'm new in PowerBi space and was hoping to get some answers here.

My data source is salesforce and excel. Using them I created a custom column with a specific formula. Will the column values will change with updated values from data source?

If not how do I create dynamic custom column?

Thank you for your help!

Please let me know if my question is not clear.

[amitchandak]

You can create a column across the table. But they will be pre-calculated. means will not have any imapct if choose any variable

refer:

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

In table one using table 2

New column in Table 1 = maxx(filter(table2,table1[customer] = table2[customer] && table2[option]="construction",table2[value])

New column in Table 1 = maxx(filter(table2,table1[Attribute] = table2[name] && table1[project] = table2[project]),table2[name]

In table 1 using table 1

new Column =
LOOKUPVALUE('Table'[Lot No.], 'Table'[Entry Type], "Output", 'Table'[Document No.], firstnonbank('Table'[Document No.],true()))
OR
new Column =
minx(filter('Table', 'Table'[Entry Type]= "Output" && 'Table'[Document No.]= earlier('Table'[Document No.])),'Table'[Lot No.])

Appreciate your Kudos.

[ST_OK]

Thanks @amitchandak  for sharing the insightful post.

To give you more calirifcation, I have 3 tables (spend data by year) and I did an append query to combine them together and created custom column i.e. Impact by year (created by respective spend for that yr). My aim is to compare the spend with impact/spend data I already have and using that to calculate new Impact by year values.

e.g Impact for 2020=(Spend 2020 * Impact)/Spend 

* I already have impact and spend data i.e. I know how impact (1000) is generated by spend (50K)

Based on your answer, I will assume the Impact for 2020 will update when I update the source table (Retriving data from salesfroce)

I tried to explain my scenario as much as I can. Waiting for t=your comment on this.

[v-juanli-msft]

Hi @ST_OK 

Do you have tables like:

year	spend	impact
2018	1	2
2019	2	3
2020	3	4

e.g Impact for 2020=(Spend 2020 * Impact)/Spend 

"Spend" is not the same with "Spend 2020", "Impact" is not the same as "Impact for 2020", right?

If so, how to define them based on my table above?

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ST_OK]

HI @v-juanli-msft 

Yes, very close. Below will be my final table where Impact is a custom column i created and using append query I have combined 3 tables of 2018^, 2019 and 2020 values.

Client	spend 2018	impact 2018	spend 2019	Impact 2019
ABC	45K	90	30K	60
Metro	90K	405	55K	247

^Table For 2018

Client	Spend	Year	Impact for 100K Spend	2018 Impact (Custom column)
ABC	45K	2018	200	=( Spend * Impact for 100K ) / 100K
Metro	90K	2018	450	=( Spend * Impact for 100K ) / 100K

"Spend" is not the same with "Spend 2020", "Impact" is not the same as "Impact for 2020", right? Yes

Q. Will my custome column values will automatically update when I update my data source? i.e when I add more spend and client info in table for 2018, 2019, 2020?

I have tried to explain as much I can. Appricitae for your help. 🙂

[v-juanli-msft · Accepted solution]

Hi @ST_OK 

If you'd like to calculate the impact for every year per client, please creat a calculated column as below:

It works when there is more data updated.

Based on my knowledge, your formula also works after more data updated.

My formula can group data based on "year" and "client".

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ST_OK]

Thanks Maggie!
Appriciate the detailed explaination. Cheers!

[piyushszope]

Yes, the values will change if the data source is refreshed.