Goal: Having all records from table which met condition, but those which still not having 'DEPARTED' status have to match with database record, till the moment of changing the status to 'DEPARTED' and then data can be frozen.
Issue #1: Joining Incremental Refresh table with Snapshot (replaced every time powerbi refresh the model) from time to time cause issue with duplicates (where incremental refresh takes KEY from other query)
Issue #2: Snapshot is grabbing dynamic data from system, I have available 'CHANGED_TIME' column, but sometimes records are not changed for many days (over 5 mostly), so I would like to avoid setting up "refresh rate" in incremental refresh for more than 5 days and still need to grab those changes in records.
Question: How to join these in PowerBI properly, or how to set incremental refresh? Any ideas? Can I do it with one query with proper IR set up?
Table 01 IR - Querying for all units with condition (1) = 'Departed' , where incremental refresh is joined with 'TIME_COMPLETE' field. Goal is to grab all records with phase which gain status 'DEPARTED' based on 'TIME_COMPLETE' value. Limited from left side, to not capture too big package.
select
*
from
Table01 UF_Table
where
UF_Table.STATE in ('DEPARTED')
and UF_Table.TIME_COMPLETE > '01-JAN-22'
Table 02 SN - querying for all units with condition (1) <> 'Departed' . Database snapshot. Records in snap are dynamic, so all fields can change from minute to minute, which needs to populated in table.
select
*
from
Table01 UF_Table
where
UF_Table.STATE not in ('DEPARTED')'