Forum Discussion
Too Many Literal Values Error when Unfiltered with Search-Based Table Visual
Hi Anonymous , thank you for the response but I was unable to solve the issue using this measure.
What worked for me was using the top N filter for (Directory_Emp[ID]) based on the First of (Directory_Emp[ID]) instead of Count of (Directory_Emp[ID]). Moreover I also noticed that my visualization worked when I changed the cardinality to many to one instead of the autodetected many to many. I noticed that using either of these made my model work and currently I am using both of them.
Hi nigama,
Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to Ahmed-Elfeel for prompt and helpful response.
This issue happens because in DirectQuery mode, Power BI tries to query all employee records (6000+ IDs) when no filters are applied, which exceeds the SQL limit of 2100 parameters. It’s not a DAX error but a technical limitation of DirectQuery.
The best way to handle this is to prevent the visual from loading until a Name or ID filter is applied. You can do this using a measure like.
Show Visual = IF(ISFILTERED(Directory_Emp[Name]) || ISFILTERED(Directory_Emp[ID]),1,0)
Then, add this measure to the visual’s filters pane and set it to show items where Show Visual = 1.
Thaks & Regards,
Prasanna Kumar
Hi Anonymous , thank you for the response but I was unable to solve the issue using this measure.
What worked for me was using the top N filter for (Directory_Emp[ID]) based on the First of (Directory_Emp[ID]) instead of Count of (Directory_Emp[ID]). Moreover I also noticed that my visualization worked when I changed the cardinality to many to one instead of the autodetected many to many. I noticed that using either of these made my model work and currently I am using both of them.