Forum Discussion
Lock the Values in a column after report refresh
Hi Claire_ ,
Unfortunately, Power BI does not have a feature equivalent to Excel's 'Paste Values' that would allow for completely static values in a column following a data refresh. However, there are some approaches you might consider to work around this issue:
1.You can create a calculated table that captures the snapshot of your data at a point in time using DAX. This table will not change until you update the DAX expression itself. For example, you can use the CALCULATETABLE & KEEPFILTERS to keep the original data.
2.If your report is not refreshing too frequently, you could potentially duplicate the relevant data into a new table using Power Query before the refresh and then use that table in your reports. However, keep in mind that this duplicate table would need to be manually updated or replaced with each refresh to maintain its 'static' nature.
KEEPFILTERS function (DAX) - DAX | Microsoft Learn
CALCULATETABLE function (DAX) - DAX | Microsoft LearnBest regards
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi Claire_ ,
Unfortunately, Power BI does not have a feature equivalent to Excel's 'Paste Values' that would allow for completely static values in a column following a data refresh. However, there are some approaches you might consider to work around this issue:
1.You can create a calculated table that captures the snapshot of your data at a point in time using DAX. This table will not change until you update the DAX expression itself. For example, you can use the CALCULATETABLE & KEEPFILTERS to keep the original data.
2.If your report is not refreshing too frequently, you could potentially duplicate the relevant data into a new table using Power Query before the refresh and then use that table in your reports. However, keep in mind that this duplicate table would need to be manually updated or replaced with each refresh to maintain its 'static' nature.
KEEPFILTERS function (DAX) - DAX | Microsoft Learn
CALCULATETABLE function (DAX) - DAX | Microsoft Learn
Best regards
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi Anonymous , thank you for the prompt response and offer of advice. I've managed to create a calculated table to give me static figures using a Dax expression:
Jan 24 = SELECTCOLUMNS(
CALCULATETABLE(
'Grouped Departmental Summary',
KEEPFILTERS('Grouped Departmental Summary')
),
"Department", 'Grouped Departmental Summary'[Grouped Department],
"% Days Lost", 'Grouped Departmental Summary'[Grouped % Days Lost],
"Month&Year", 'Grouped Departmental Summary'[Month&Year]
)
So far this has worked. I want to build a year-to-date picture too for a model. Am I best using a calculated table for this too? I have separate tables for each month.Just another question on this. I ran a test refresh to move the Grouped Departmental Summary into February and it also refreshed the data in my calculated table.
You mentioned above about manually updating or replacing the table. Is there another step I need to add to this calculated table so that it doesn't change in the future?
It will be recalculated during every semantic model refresh.
To keep it static you would need to create it in Power Query and disable refresh, or store the snapshot further upstream, or reconsider the entire approach as yours may not be sustainable.
Ah, so is it simply a case of unticking the 'Include in Report Refresh' after I've built a calculated table in Power Query? I've a few tables like that
