Forum Discussion
Save Measures or Calculated Column Values in a seperate table
I have one flat file. A full refresh occurs daily. Within the table are the day's current status. Number Pending, New Orders, etc...
In turn, I have visuals that show the counts for the current day. What I want is tomorrrow, when the full refresh occurs it wipes out yesterday. I want to save Yesterday's totals for the categories I'm publishing. I want this to occur after every dayily refresh. The purpose is to store historical counts and produce reports showing trends. The data model at the moment has only one flat file. How can I achieve the goal of saving critical counts for each day ?
Anonymous wrote:What would be required to create this from scratch?
it depends on which technologies you have at your disposal:
- one of the possible solution is in my previous post: Data Flow Gen. 2 -> Lakehouse -> Power BI Dataset -> Report (NB: requires Fabric/Premium capacity that is in preview)
- you could use SQL Server Integration Services
- you could use Azure Data Factory
- ...
6 Replies
I don't think you can do it with Power BI only.
You should read the data daily and store them somewhere (Azure SQL DB, Datalake, Sharepoint... whatever).
Append to the records the date of the snapshot so you can idenity snapshot date.
Having a daily copy of the data, building a dataset that has everything in it is as simple as that.
Having a Premium/Fabric Capacity you could build a Dataflow Gen 2 (if you like PQ) that reads the file and happends it in a lakehouse table daily. When the table is there you can build the report using the default dataset exposed by lakehouse.
OK, so I've decided to create one master file in the cloud environment and use the SQL souce to copy and append to this master on a scheduled basis. What would be required to create this from scratch? Once created, I want to link my Power PI front end report to the newly created master file. It will be the only table I work with.
when developing dataflow you have chosen to append or to replace data in the destination table in the lakehouse. according to what you have chosen refreshing dataflow will append or truncate/insert data into lakehouse. with data in lake you can read them with power bi.
OK, so I've decided to create one master file in the cloud environment and use the SQL souce to copy and append to this master on a scheduled basis. What would be required to create this from scratch? Once created, I want to link my Power PI front end report to the newly created master file. It will be the only table I work with.
Anonymous wrote:What would be required to create this from scratch?
it depends on which technologies you have at your disposal:
- one of the possible solution is in my previous post: Data Flow Gen. 2 -> Lakehouse -> Power BI Dataset -> Report (NB: requires Fabric/Premium capacity that is in preview)
- you could use SQL Server Integration Services
- you could use Azure Data Factory
- ...