PBI Best Practices - Highlights from the Indexima team
Use KEEPFILTERS
When possible, use KEEPFILTERS instead of FILTER.
More information visit https://www.sqlbi.com/articles/using-keepfilters-in-dax/
Consider Deactivating slider filters
When creating multiple sliders, the default behavior would be "when a user clicks on a slider, the other sliders will be refreshed considering the first filter". The refresh costs computation for both PowerBI and Indexima.
Go to Microsoft documentation for more information.
Driver option: Use NativeQuery True
Choosing the option "Use NativeQuery= True" will avoid an extra conversion of the SQL query, thus improving overall performance.
This option also makes PowerBI ask the same SQL queries when a user is refreshing data (otherwise, SQL queries contain Aliases with uniqueID making queries unique). Having the opportunities to generate the same SQL queries allows using the Indexima cache, thus improving the general performance, especially in case of heavy use of the cluster.
It requires having different columns for all tables.
Date Filtering
When having a star schema using the Date (Dimension) table, Go to the Date Dimension Table section. This table contains, on purpose, two identical date fields:
- Use the field
Dateto filter in PowerBI - Use the field
Date_for_jointo join the date table and the date in the fact table. To avoid confusion, this field can be hidden from users.
Failing to separate the usage of those fields may result in errors from PowerBI (the most common being the "Query Folding" issue).