Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Claire_
New Member

Lock the Values in a column after report refresh

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

1 ACCEPTED SOLUTION
v-heq-msft
Community Support
Community Support

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, k
eep 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

View solution in original post

10 REPLIES 10
Claire_
New Member

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
Community Support
Community Support

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, k
eep 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

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

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.

great 🤣

refactor your process and eliminate the need for static snapshots.

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
Super User
Super User

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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.