Forum Discussion
Resolver Iadding non-parameterized conditions to semantic model incremental refresh
Thank you again nilendraFabric -- your thoughtful reply led me to the solution.
Indeed it was separating the two filter statements that was the key, because this allowed me to see the issue was simply with my syntax on the AuditIsDeleted column. It's a bit/boolean column, and as shown in my query snippet above, I was trying to compare it in the filter to a value (0 or 1).
But in fact since PQ sees it as a true/false, no comparison is needed to get a true/false for the criterion -- the column itself is the true/false. So I changed my filter expression to:
= Table.SelectRows(Dimensions_Status, each [AuditPartitionDay] >= RangeStart and [AuditPartitionDay] < RangeEnd and not([AuditIsDeleted]))
and it works as expected now! Isolating that last part into its own statement let me work the syntax until I understood my error. Then I just combined it all back into one filter expression and it's good.
Thank you again for your help. Very much obliged.
Super UserHello markmsc
Instead of adding the AuditIsDeleted condition directly to the main filter, you can use a custom detect data changes query in your incremental refresh policy. This allows you to include additional conditions without interfering with the main date-based partitioning.
In the incremental refresh settings, under “Detect data changes,” select “Use custom query” and provide a query that includes both the date range and the AuditIsDeleted condition. For example:
SELECT MAX(AuditPartitionDay) as MaxDate
FROM Dimensions_Status
WHERE AuditPartitionDay >= @RangeStart
AND AuditPartitionDay < @RangeEnd
AND AuditIsDeleted = 0
please make sure that your data source supports query folding for the custom query. This is crucial for the incremental refresh to work efficiently.
Keep the main filter in your Power Query as it was originall.
= Table.SelectRows(Dimensions_Status, each [AuditPartitionDay] >= RangeStart and [AuditPartitionDay] < RangeEnd)
After the date-based filter, add another step to filter out deleted rows:
= Table.SelectRows(#"Previous Step", each [AuditIsDeleted] = 0)
Hopefully it will work.
please give kudos and accept this solution if this resolved your query.
Thanks
Resolver IThank you again nilendraFabric -- your thoughtful reply led me to the solution.
Indeed it was separating the two filter statements that was the key, because this allowed me to see the issue was simply with my syntax on the AuditIsDeleted column. It's a bit/boolean column, and as shown in my query snippet above, I was trying to compare it in the filter to a value (0 or 1).
But in fact since PQ sees it as a true/false, no comparison is needed to get a true/false for the criterion -- the column itself is the true/false. So I changed my filter expression to:
= Table.SelectRows(Dimensions_Status, each [AuditPartitionDay] >= RangeStart and [AuditPartitionDay] < RangeEnd and not([AuditIsDeleted]))
and it works as expected now! Isolating that last part into its own statement let me work the syntax until I understood my error. Then I just combined it all back into one filter expression and it's good.
Thank you again for your help. Very much obliged.
- nilendraFabric
Hi markmsc Glad it worked out. Not sure if you can mark your reply as solution.
getting green tick helps the community to go to the right answer quickly and trust the discussion.
thanks