Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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".
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.
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.
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.
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. 🙂
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".
Thanks Maggie!
Appriciate the detailed explaination. Cheers!
Yes, the values will change if the data source is refreshed.