Getting error in Power BI: SQL compilation error: In-list contains more than 50 non-constant values

[Anonymous]

My backend data base is Snowflake and we have a direct query connection with Power BI. 

Some of my visulas are giving the below mentioned error suddenly. 

There is one visual level filter (using measure)  applied on this visual, which is  - # No of Employees > 0. (When I am removing this filter, I am not getting the error. But with this filter I am getting the error)

The measure is - count(# No of Employees = Table[Employee_Key]).

SQL compilation error: In-list contains more than 50 non-constant values: COLLATE_TO_BINARY('Headquarters LDB', 'en-ci'),COLLATE_TO_BINARY('DEG L''Oreal UK', 'en-ci'),COLLATE_TO_BINARY('DPP Chile', 'en-ci'),...

What can be done differently to solve this issue.

[rajendraongole1 · Accepted solution]

Hi @Anonymous - we will see these errors indicates that the SQL query generated by Power BI is exceeding Snowflake's limit for the number of non-constant values allowed in an IN clause.Instead of filtering at the visual level, try aggregating the data first like preaggregate the employee count.

later you can implement Row-Level Security to filter data at the data model level rather than at the visual level. This can help reduce the amount of data that needs to be processed by the visual and avoid hitting the Snowflake limit.

Simplify or adjust the logic of your filter to reduce the number of unique values.

Hope it helps.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!