Monitoring CRUD changes in Power BI should be followed with best practices to track the changes that occur in the data set over time.
Include timestamps for changes: Importantly, ensure that your data source has timestamp columns, such as CreatedDate and UpdatedDate, to record the date and time when the records were created or modified. For Delete operations, use a flag or status column (e.g., IsDeleted or Active) to indicate whether a record was deleted.
Historical Tracking Data Modeling:
Slowly Changing Dimension (SCD) – Historical records – this is, for example, if an update occurs to a record, the old version is stored together with the new one.
If necessary, keep separate tables for each CRUD or track changes within the same table using additional flags for each CRUD operation.
DAX Calculations for CRUD Operations:
Create: Find new records by comparing the current data to the last version (use DAX functions like EARLIER() or LOOKUPVALUE() to compare previous and current states).
Update: Use IF() or SWITCH() of DAX to flag the updated rows. Track updates by checking whether the value has changed for existing records.
Delete: Deleted records are flagged using a flag in the data model, which differentiates them from active records. Hence, deleted records can be filtered in visualizations, or DAX can be used to count or aggregate only non-deleted records.
Read Action. This is generally what is meant by querying the data without modifying it. Read-actions can be tracked as access logs, or how many times records have been accessed, in case it is possible to track access logs.
Audit Tables: Include an audit table that details which type of operation (Create, Read, Update, Delete) is performed and by which user or system. This will allow you to create a full audit trail that can be visualized in Power BI.
