Using list.contains function blocks data refresh in service

[DNKL]

Hi all, 

In a Power BI report, I have used the following function to limit the amount of data that is being imported into the model: 

= Table.SelectRows(#"Added Items", each List.Contains(Partners_Filter, [Customer.Customer Code]))

The function is part of the M code for a retrieving a dimension table from SQL Analysis Services, and the 'Partners_Filter' refers to a table based on CRM data, which is turned into a 'list' in Power Query.

When I refresh the model in Power BI desktop, I am able to refresh the model without any issues. 

However, as soon as I publish the report, connect it to a gateway and then try to update the report, I get the same error every time, each time referring to a table where I have used the List.Contains function: 

I have tried to reboot the gateway and I have tried to add a delay to the dimension table, but the only thing that lets me refresh the model is if I remove this line from the query. 

However, it is a crucial part of the model, since I am working with cubes and I cannot continue building a table if I try to filter the input in any other way. 

Has anyone encountered this issue before, and found an alernative, or know what the issue may be? 

Please let me know if you need any further information. 

Thank you in advance!

[v-lionel-msft]

Hi @DNKL ,

Has your problem been solved?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DNKL · Accepted solution]

Hi Lionel, 

Thank you for checking in! 

After a few weeks of using a work-around, I found out by chance that the problem was resolved by changing the location settings of the dashboard. No idea why this was the issue - it has been quite frustrating. 

Best, 

DNKL

[v-lionel-msft]

Hi @DNKL ,

What's the version of your gateway ?

Try updating to the latest version if the gateway is not the latest version.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.