Using a text filter and getting around Direct Query row limit

[ali_iopp]

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.

[ali_iopp]

@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]

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

[ali_iopp]

I think this wouldn't work for my use case, the issue is that report users want to search for an AssetID themselves and this solution I think does not work dynamically?

[v-achippa]

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

[v-achippa]

Hi @ali_iopp,

We wanted to kindly follow up to check if the solution I have provided for the issue worked.
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

[ajohnso2 · Accepted solution]

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]

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]

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