I don’t know if this is possible, but I’d like to create a table that builds with each refresh. Here’s what I’m thinking:
I have a project DB that I pull in a table that has a toggle – active or non-active. Using that toggle, I can filter all measure to calculate on active projects only.
Projects from the source DB are toggled from active to non-active and vice versa without a date being attached. So when I refresh, I get today’s active project but no context to how it may compared to yesterday
Is it possible to create a table that is filled by a measure each day I refresh. The parallel is when using excel you copy a formula cell and you paste it as a number.
Over time (as I refresh daily), I get a table that is built with a new row each refresh.
I want to calculate a measure each day based on the day’s refresh, and then store that measure as a number in a seperate table.
I hope that makes sense any feedback is greatly appreciated.
If I’ve understood correctly, you want to capture snapshots and append them overtime/refreshes when importing.
I’m not aware of any way that could be done solely within Power BI without having a date field in the source. If you had a date you could look at deploying an incremental refresh policy. This would append ‘new’ data by checking the date field.
Thanks for posting your question @doug99. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.
Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, a screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
Including all of the above will likely enable a quick solution to your question.
If the report uses a SQL Query to return the results, you can create some custom M code to compose a new SQL query that includes an INSERT statement before the normal SELECT statement, e.g.,
INSERT INTO Person (FirstName, LastName) VALUES (‘John’, ‘Doe’)
SELECT p.FirstName, p.LastName FROM Person AS p
I’m guessing this will be called before a measure is calculated, so I’m not sure this would meet your requirements at all. Also, please note that you’d have to give the (service) account used in the report write access to the SQL database behind your report (instead of read-only), and this may create security implications for your organization …
Hate to be negative but i don’t think Power BI can store variables over time after a new refresh is done. I would love to see an option for that i hope in the future.
Hi @doug99, we’ve noticed that no response has been received from you since the 25th of May. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
@doug99 The best way we’ve found to accomplish what you’re looking for is to use Dax Studio. I described it in a separate post which @marcster_uk linked earlier.
There is no way we’ve found to do it in one model but if you’re willing to work outside with Dax Studio it is possible.
Let’s say that Table A is the one you want a snapshot of. What you’ll do, while the pbix file is open, is connect Dax Studio to the model. In the DAX window, enter the command EVALUATE Table A. You then select an output method (I usually use csvs) and hit Run in the ribbon. A record counter will tell you when the table has been exported. You would then bring it back into the model as a new dataset, which you can append others to over time.