Forum Discussion

nigama's avatar
nigama
Advocate I
10 months ago
Solved

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

I’m working with a Power BI report that uses DirectQuery to connect to three Dataverse tables: Directory Table – Contains over 6,000 employee records with fields like Name and ID. Scanner Data Tab...
Need Help
  • nigama's avatar
    nigama
    10 months ago

    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. 

Ahmed-Elfeel's avatar
Ahmed-Elfeel
Super User
10 months ago

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's avatar
nigama
Advocate I
10 months ago

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.