Forum Discussion

ali_iopp's avatar
ali_iopp
Frequent Visitor
1 year ago
Solved

Using a text filter and getting around Direct Query row limit

Hi,
I would like for users to enter an `Asset ID` (or collection of `Asset ID`s) into a text slicer, this is linked to a table of records related to the Asset ID. The issue I have is that due to volume of data I am having to use Direct Query mode, using Import mode the .pbix file is nearly 2GB and it will only grow in the future.

Direct query has a row limit of 1,000,000 rows per query/visual, so with no filters applied the resultant table is blank with the `Error fetching data for this visual` message. I could live with this, but the slicer which uses the Asset ID also produces this error (there are slightly over 1million values) so I cannot attempt to slice the data.

I have used the built-in `Text Slicer`, plus the additonal visuals `Text Filter` from Microsoft, the `Text search slicer` from databrothers.cz, and the `Inforiver Super Filter` but none of them allow for proactively entering values to filter on, is this possible? Currently I am having to do some awkward workaround with other filters to filter the Text Filter, but that will likely confuse users.

Need Help
Tips and Tricks
  • ajohnso2's avatar
    ajohnso2
    1 year ago

    Hi, have you considered using incremental refresh on your model? This will enable you to keep your pbix file relatively empty whilst building history on your deployed model in the service.

8 Replies

  • ajohnso2's avatar
    ajohnso2
    Solution Supplier
    1 year ago

    Hi, have you considered using incremental refresh on your model? This will enable you to keep your pbix file relatively empty whilst building history on your deployed model in the service.

    • ali_iopp's avatar
      ali_iopp
      Frequent Visitor
      1 year ago

      Though this isn't really a solution to the stated problem, I was able to use the incremental refresh in addition to model improvements to change the report away from direct query mode and thus solved the issue of the 'broken' slicer, thanks for the tip ajohnso2 

      • v-achippa's avatar
        v-achippa
        Community Support
        1 year ago

        Hi ali_iopp,

         

        Thank you for the response and confirming that the issue is resolved. I request you to please accept the post as "Accept as Solution" so that other community members who has similar issue will find it more easily.

        Thank you for being part of  Microsoft Fabric Community.

         

         

        Thanks and regards,

        Anjan Kumar Chippa

  • ali_iopp's avatar
    ali_iopp
    Frequent Visitor
    1 year ago

    ajohnso2 it's not something I have looked at but that might be better than trying to get it to work with Direct Query mode. As long as I am able to use incremental refresh on mulitple datasets (I have both a dimension and fact table with >1 million rows) I will try that, thanks for the tip.

    • v-achippa's avatar
      v-achippa
      Community Support
      1 year ago

      Hi ali_iopp,

       

      Thank you for reaching out to Microsoft Fabric Community.

       

      Please follow below steps:

      • Create a small table using Enter Data for user entered Asset IDs and add the AssetId data manually, do not create a relationship between this table and your main fact table.
      • Now create a DAX measure in the main fact table like this below:

        Filter_AssetID =

        IF (

            SELECTEDVALUE(FactTableName[AssetID]) IN VALUES(AssetID_Input[AssetID]),

            1,

            0

        )

      This measure checks whether the current row's Asset ID exists in the input list.

       

      • Go to the visual and in the Filters pane, drag the Filter_AssetID measure into the visual level filter section and set it to:

        Filter_AssetID is 1

      This will show only the records matching the entered Asset IDs

       

      If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

       

      Thanks and regards,

      Anjan Kumar Chippa

      • v-achippa's avatar
        v-achippa
        Community Support
        1 year ago

        Hi ali_iopp,

         

        As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked?  or let us know if you need any further assistance.
        If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

         

        Thanks and regards,

        Anjan Kumar Chippa