Forum Discussion

lucidum's avatar
lucidum
Frequent Visitor
1 day ago

Cross Filtering on long/special char fields error

On a recent deployment to powerbi service, cross filtering stopped working on a certain free text fields. Everything worked fine in powerbi desktop. When published to the web the cross filtering context stopped working. The was i resolved the issue is that i didnt really want to filter on longtext field, but by the nature of cross filtering, i didnt have a choice. So i relaced the column with a measure to ignore the cross filtering for that field. 

Tips and Tricks

2 Replies

  • jubinsoni's avatar
    jubinsoni
    Icon for Solution Sage rankSolution Sage
    1 day ago

    Hi lucidum​,

    Thanks for posting what worked. I have not reproduced the service-versus-Desktop difference myself, so I cannot say why the long text column behaves differently there, but the pattern you landed on is a sound one: do not let a long free-text column take part in filter context.

    A few ways to get the same result with less rework:

    Edit interactions -> turn cross-filtering off from the source visual to the affected visuals
Key column       -> filter on a short ID or hash column; show the long text only as a display field
Hide the column  -> hide the free-text column in model view so it is not used as a slicer or filter
Measure          -> as you did, return the text with a measure so it is not a filter field

    If you keep the measure approach, a small version for display-only text looks like this:

    Comment Text =
SELECTEDVALUE ( 'Cases'[Comment], "Multiple values" )

    A short ID column also keeps the model smaller, since high-cardinality long strings are among the most expensive columns to store.

    So my overall approach would be:

    Filter on keys -> short IDs drive cross-filtering
Display text   -> measures or tooltips, never filter fields
Tune           -> Edit interactions for visuals that should not react

    AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    7 hours ago

    Hello lucidum​,

    For a more robust solution, I'd recommend using a unique numeric ID or surrogate key for filtering, while keeping the original text field for display.

    I find that this avoids relying on lengthy text values in the filtering context.

    Though, if the issue persists with numeric keys, I'd investigate the Service-generated queries using Performance Analyzer and consider raising a Microsoft support case, as the Desktop versus Service difference could indicate a regression.

    Hope this helps!