Too Many Literal Values Error when Unfiltered with Search-Based Table Visual

[nigama]

I’m working with a Power BI report that uses DirectQuery to connect to three Dataverse tables:

1. Directory Table – Contains over 6,000 employee records with fields like Name and ID.
2. Scanner Data Table – Captures data when employees scan their ID at locations like the cafeteria or gym. Fields include ID, Name, Timestamp, and Location.
3. Manual Logging Table – Populated when employees manually log their presence at a location through our app. This also includes ID, Name, Timestamp, and Location.

Both the Scanner and Manual Logging tables are related to the Directory table via the ID field, with one-way filters to the Directory to the other two.

Goal:

I’m building a search functionality in Power BI where users can enter a Name or ID to view the latest location and timestamp for an employee. I’m using a table visual with the following fields:

• Name
• ID
• Latest Location
• Latest Timestamp

The search is implemented using text filters on Name and ID.

DAX Measures:

To determine the latest location, I created the following measures:

Latest Scanned Time =
CALCULATE(
    MAX(Scanner_Data[Modified On]),
    FILTER(
        Scanner_Data,
        Scanner_Data[ID] = SELECTEDVALUE(Directory_Emp[ID])
        && Scanner_Data[Modified On] >= TODAY() - 2
    )
)

Latest AppLogged Time =
CALCULATE(
    MAX(Logging_App_Data[Modified On]),
    FILTER(
        Logging_App_Data,
        Logging_App_Data[ID] = SELECTEDVALUE(Directory_Emp[ID])
        && Logging_App_Data[Modified On] >= TODAY() - 2
    )
)

Latest Location =
VAR ScanTime = [Latest Scanned Time]
VAR AppTime = [Latest AppLogged Time]
VAR ScanLoc =
    CALCULATE(
        MAX(Scanner_Data[Location]),
        FILTER(
            Scanner_Data,
            Scanner_Data[ID] = SELECTEDVALUE(Directory_Emp[ID])
            && Scanner_Data[Modified On] = ScanTime
        )
    )
VAR AppLoc =
    CALCULATE(
        MAX(Logging_App_Data[Location]),
        FILTER(
            Logging_App_Data,
            Logging_App_Data[ID] = SELECTEDVALUE(Directory_Emp[ID])
            && Logging_App_Data[Modified On] = AppTime
        )
    )
RETURN
    SWITCH(
        TRUE(),
        NOT ISBLANK(ScanTime) && ScanTime > AppTime, ScanLoc,
        NOT ISBLANK(AppTime), AppLoc,
        "Unknown"
    )

Issue:

When no filters are applied (i.e., default state of the visual), I get an error:

"Error fetching data"

Clicking on “See details” shows:

DataSource.Error: Microsoft SQL: {"Message":"Too many literal values were passed in the query. The maximum number is 2100 Line:30, Position:25213"}

However, once I type something into the search box and the table is filtered, everything works perfectly and displays the expected results.

Constraints:

• I’m using DirectQuery and cannot switch to Import mode.
• I prefer not to create new tables in Power BI, as I want this visual to function like a live search engine.
• I’d like to avoid editing the source tables unless absolutely necessary.

[v-pgoloju]

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

[nigama · Accepted solution]

Hi @v-pgoloju , 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. 

[Ahmed-Elfeel]

Hi @nigama,

This is a DirectQuery limitation where Power BI tries to pass all the unfiltered values (all 6000+ employee IDs) to the SQL query (exceeding the 2100 parameter limit)

Here are several solutions:

1-Optimize Your DAX Measures (The main issue is using SELECTEDVALUE(Directory_Emp[ID]) in filters)

Replace your measures with this more efficient approach:

Latest Scanned Time =
VAR CurrentID = SELECTEDVALUE(Directory_Emp[ID])
RETURN
IF(
    NOT ISBLANK(CurrentID),
    CALCULATE(
        MAX(Scanner_Data[Modified On]),
        Scanner_Data[ID] = CurrentID,
        Scanner_Data[Modified On] >= TODAY() - 2
    )
)

Latest AppLogged Time =
VAR CurrentID = SELECTEDVALUE(Directory_Emp[ID])
RETURN
IF(
    NOT ISBLANK(CurrentID),
    CALCULATE(
        MAX(Logging_App_Data[Modified On]),
        Logging_App_Data[ID] = CurrentID,
        Logging_App_Data[Modified On] >= TODAY() - 2
    )
)

Latest Location =
VAR CurrentID = SELECTEDVALUE(Directory_Emp[ID])
VAR ScanTime = [Latest Scanned Time]
VAR AppTime = [Latest AppLogged Time]
RETURN
IF(
    NOT ISBLANK(CurrentID),
    SWITCH(
        TRUE(),
        NOT ISBLANK(ScanTime) && ScanTime > AppTime, 
            CALCULATE(MAX(Scanner_Data[Location]), Scanner_Data[Modified On] = ScanTime),
        NOT ISBLANK(AppTime),
            CALCULATE(MAX(Logging_App_Data[Location]), Logging_App_Data[Modified On] = AppTime),
        "Unknown"
    )
)

2-Add a simple filter to prevent the unfiltered state:

• Add a slicer with All selected by default

• Or add a text filter that defaults to showing only recent records:

Show Recent Only = 
SELECTEDVALUE(Directory_Emp[ID]) IN VALUES(Scanner_Data[ID]) || 
SELECTEDVALUE(Directory_Emp[ID]) IN VALUES(Logging_App_Data[ID])

3-Create a calculated table (if you can make an exception) that preaggregates the latest locations:

Employee Latest Location =
SUMMARIZE(
    Directory_Emp,
    Directory_Emp[ID],
    Directory_Emp[Name],
    "Latest Location", [Latest Location],
    "Latest Timestamp", GREATEST([Latest Scanned Time], [Latest AppLogged Time])
)

•  Then use this table for your search visual.

 Let me know if this works ❤️☺️

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[nigama]

Thank you @Ahmed-Elfeel  for the detailed optimization steps.

Unfortunately, I was not able to resolve the issue using the suggested steps including creating the calculated table.

Currently I am working on trying to hide my visualization when no filters have been applied using ISFILTERED.