Lock the Values in a column after report refresh

[Claire_]

Help, before I throw PBI out the window.

I really hope I'm missing something obvious about this! I have multiple tables in my report which are refreshed monthly. I've built a year-to-date table that tracks and summarises the monthly figures. I've merged a column from the monthly table (which is dynamic and refreshes) to the yearly table (which I want to be static), but I can't get the figures to stay static after the refresh. Is there a dax expression or power query value you can add after the add column function?

Any help would be appreciated. If we had a 'paste values' option like excel, it would solve all my problems.

Thanks in advance

[Claire_]

Hi @lbendlin thank you so much for the offer of support. I was getting stuck trying to take the figures from my master table (Grouped Departmental Summary) to create a static table with the % Days lost for that month. I was using PowerQuery Editor to do this and I think this is where I was going wrong. @lbendlin pointed out it should be a calculated table and after I applied the below DAX, this seems to have worked. 

Snapshot = 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]
)

I just need to figure out how to build a year to date picture with each of these months

[v-heq-msft · Accepted solution]

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

[Claire_]

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?

[lbendlin]

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.

[Claire_]

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

[lbendlin]

yes, but this is DANGER ZONE.  Even if you specify that, Power Query may still refresh the data source if it is part of query dependencies.

[Claire_]

great 🤣

[lbendlin]

refactor your process and eliminate the need for static snapshots.

[Claire_]

Hi @v-heq-msft , 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.

[lbendlin]

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?