Forum Discussion
Slicer visual problem
It seems the Slicer member list is incorrect in DirectQuery against Oracle for a Text column with numeric and alphanumeric values; Search/Table/Filter Pane show all values; changing the table to Dual resolves it. Is this a bug ?
Hi John_Fjord -This looks like a potential DirectQuery-specific issue, but I wouldn't classify it as a confirmed bug yet. Since the values appear correctly in the Table visual, Filter pane, and slicer search, but not in the slicer list, I would suggest checking the following:
1. Verify that the Oracle column is defined as VARCHAR2/NVARCHAR2 and its Power BI data type is Text. 2. Check for any visual-level filters, relationships, or interactions affecting the slicer. 3. Use Performance Analyzer to compare the DAX queries generated by the slicer and Table visual. If possible, inspect the corresponding Oracle SQL for implicit conversions or differences in distinct-value retrieval. 4.Test the same column in a new slicer on a blank report page. **Using Dual storage mode is a valid workaround**, especially for dimension tables, because Power BI can retrieve slicer values from its in-memory cache instead of querying Oracle directly.
If the issue is reproducible in DirectQuery with identical filter conditions, I recommend raising a Microsoft Support ticket with a sample PBIX and query traces.
Fabric community support | Microsoft Fabric Community
Reference: Table Storage Mode in Power BI Semantic Models - Power BI | Microsoft Learn
Hope this helps!
3 Replies
jubinsoniSkilled Sharer
Hi John_Fjord,
It may well be a bug, but there is a specific reason the slicer can behave differently from the table and filter pane in DirectQuery: the slicer sends its own query (distinct values, sorted, with a row limit), while the other visuals filter or group. With a text column that mixes numeric and alphanumeric values, the Oracle side of that query is sensitive to sorting and comparison settings.
Things worth checking:
1. Performance analyzer > refresh the slicer > Copy query -> run the generated SQL directly in Oracle and compare with what the slicer shows 2. Oracle session sort/compare settings SELECT parameter, value FROM nls_session_parameters WHERE parameter IN ('NLS_SORT', 'NLS_COMP'); -> a linguistic sort can order "10", "9", "A1" differently than binary, and the slicer's top-N window can then cut off different members 3. Hidden differences in the values: trailing spaces, CHAR vs VARCHAR2 padding, or values that look numeric but are stored with leading zerosYour Dual workaround is also the recommended design for this pattern rather than just a fix: keep the large fact table in DirectQuery and put slicer/dimension tables in Dual (or Import), so slicers are answered from the in-memory copy and the source only gets the filtered fact queries.
If the copied SQL returns the correct full list in Oracle but the slicer still shows a different one, that is worth reporting with the query and your Desktop version, since it points to how the result is being processed rather than the source.
So my overall approach would be:
Copy the slicer query from Performance analyzer -> Run it in Oracle, compare results -> Check NLS_SORT / NLS_COMP and CHAR padding / trailing spaces -> Keep dimension tables in Dual mode (best practice anyway) -> Report if the SQL is right but the slicer is wrongAI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
- rajendraongole1
Super User
Hi John_Fjord -This looks like a potential DirectQuery-specific issue, but I wouldn't classify it as a confirmed bug yet. Since the values appear correctly in the Table visual, Filter pane, and slicer search, but not in the slicer list, I would suggest checking the following:
1. Verify that the Oracle column is defined as VARCHAR2/NVARCHAR2 and its Power BI data type is Text. 2. Check for any visual-level filters, relationships, or interactions affecting the slicer. 3. Use Performance Analyzer to compare the DAX queries generated by the slicer and Table visual. If possible, inspect the corresponding Oracle SQL for implicit conversions or differences in distinct-value retrieval. 4.Test the same column in a new slicer on a blank report page. **Using Dual storage mode is a valid workaround**, especially for dimension tables, because Power BI can retrieve slicer values from its in-memory cache instead of querying Oracle directly.
If the issue is reproducible in DirectQuery with identical filter conditions, I recommend raising a Microsoft Support ticket with a sample PBIX and query traces.
Fabric community support | Microsoft Fabric Community
Reference: Table Storage Mode in Power BI Semantic Models - Power BI | Microsoft Learn
Hope this helps!
Hi,
I have already checked most of your suggestions, but I have concluded that the data has been loaded correctly—it simply isn't being displayed correctly.
If I sort the list in ascending order, I see one half; if I sort it in descending order, I see the other half.
If I use the search function, I can find all the values—they just aren't all displayed at the same time.
I could add a new column with at switch beween the 2 types, and it workedI was simply wondering whether this is a new feature or something that looks like a bug.
Thanks for the help.
