Forum Discussion
Slicer search does not work with Direct Query
Hello, I ran into a problem with the slicer search not working with data from DirectQuery.
Questions
- Is there a reason why this does not work with DirectQuery?
- Is there a plan to extend the functionality to work with DirectQuery?
- Are DirectQuery reports not the preferred way to build reports?
Hi Anonymous,
There are these main reasons why the search slicer doesn't work in direct query mode:
- DirectQuery sends queries back to the data source with each interaction. Implementing search within slicers would potentially send numerous queries, leading to performance issues and slower response times
- Certain transformations and complex queries might not be supported by the underlying data source, restricting the ability to implement full search functionality
To make better use of data source direct queries, we recommend that you follow these principles:
- Keep your data model as simple as possible, for example, avoiding bi-directional relationships is a good idea.
- Try to implement as much of the logic for your calculations in your data source and reduce the amount of DAX you need to write.
- Try to write your DAX in a Different way that Power BI will be able to fold it, if we can see the View Native Query, this means power bi can fold it.
In many cases, Import mode is preferred due to better performance and full data transfer features.
And Direct Query is designed to connect to data sources that have large data volumes and near real-time data.
Lastly, the choice between Import and Direct Query should be based on your specific requirements, such as data size, refresh needs, and performance considerations
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Anonymous,
There are these main reasons why the search slicer doesn't work in direct query mode:
- DirectQuery sends queries back to the data source with each interaction. Implementing search within slicers would potentially send numerous queries, leading to performance issues and slower response times
- Certain transformations and complex queries might not be supported by the underlying data source, restricting the ability to implement full search functionality
To make better use of data source direct queries, we recommend that you follow these principles:
- Keep your data model as simple as possible, for example, avoiding bi-directional relationships is a good idea.
- Try to implement as much of the logic for your calculations in your data source and reduce the amount of DAX you need to write.
- Try to write your DAX in a Different way that Power BI will be able to fold it, if we can see the View Native Query, this means power bi can fold it.
In many cases, Import mode is preferred due to better performance and full data transfer features.
And Direct Query is designed to connect to data sources that have large data volumes and near real-time data.
Lastly, the choice between Import and Direct Query should be based on your specific requirements, such as data size, refresh needs, and performance considerations
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks Joyce. In my case the query was very simple and was reading a very small dataset (less than 10k records) and reading from a single datasource, no DAX was needed. I built in DirectQuery mode thinking I could enable the automatic page refresh. But had to convert to ImportMode to get the slicer working.