adding non-parameterized conditions to semantic model incremental refresh

[markmsc]

Hello to the community.

Our semantic model uses date-based incremental refresh. All tables have a PQ filter on them to select rows for refresh that looks like this:

= Table.SelectRows(Dimensions_Status, each [AuditPartitionDay] >= RangeStart and [AuditPartitionDay] < RangeEnd)

Where Dimensions_Status and AuditPartitionDay are the table and a column, respectively.

This works fine.

However the model is geting large, and includes rows from tables that we don't really need to have in there, in particular rows that have been marked deleted.  So I'd like the IR policy to see and filter out those rows on the next refresh whenever some have been marked deleted since the last refresh.

I've updated the filter expression like so:

= Table.SelectRows(Dimensions_Status, each [AuditPartitionDay] >= RangeStart and [AuditPartitionDay] < RangeEnd and [AuditIsDeleted] = 0)

where AuditIsDeleted is a bit column on the table.

When I do this, the refresh now finds no rows whatsoever, even though there are rows that meet the criteria.  If I dump the table from the model entirely and do a full refresh of it from scratch, i.e. not incremental, it finds no rows, either.

I guess my basic question is, is this an invalid thing to attempt to do in a partition-based IR model?  Or altogether invalid?  I'm puzzled why adding the extra column to the filter expression excludes everythinfg regardless of which value I am trying for it to find.

[v-karpurapud]

Hi @markmsc 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-karpurapud]

Hi @markmsc 
Hope you are doing well!

Could you please confirm if your query have been resolved the solution provided by @nilendraFabric  If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you

[nilendraFabric]

Hello @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

[markmsc · Accepted solution]

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.

[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

[markmsc]

Lots to think about here, @nilendraFabric .  Thank you very much!

For Detect Data Changes, we currently used a column-based defintion there looking at a different column, AuditModifiedDate.  Any row that is updated to have its AuditIsDeleted column set will also have its modified date updated.  So I am thinking that deletes ought to be evaluated, and then it's just a matter of making the row filters work correctly.  Or at least, I'll start with the row filter part of your suggestion and then move to data changes as needed.

On the row filters, I had obviously not thought about breaking the filter process into two steps!  I'm going to do that now and see what I get.  I'll come back and update with my findings.  

Thank you again for your detailed reply!