How to improve the performance of a visual?

[Anonymous]

Hello,

I have a table visual that takes too long to load.

I have some dimensions tables and two fact tables. The "fact table 1" has active relationships with all dimesion tables, while "fact table 2" has disable relationships.

I need to  include in the report some sliders with fields from the dimension tables, to filter both fact tables.

For this purpose, in the visuals with data from "fact table 2" I have included the following measures (one for each dimension table):

 Measure = CALCULATE(SUM(FACT TABLE 2[value1]), USERELATIONSHIP(DIMENSION X[FIELD_X],(FACT TABLE 2[FIELD_X]))

And I filter the visual applying Measure > 0. This way the filters work fine, but the loading of the visual is really slow.

This is the data model:

How can I improve this?

Thank you in advance.

[Anonymous · Accepted solution]

Here are some suggestions to improve the performance:

• Fine-tune your ETL: ​​​​​​​It is best practice to not import any columns or rows of data that you do not need, so try to not even import them when loading data into Power BI Desktop.

• After importing your data, evaluate the column data types to ensure that each one is correct, because incorrect data types will lead to report performance issues and unexpected results.

• Review the column quality, which can be found under the View tab. The column quality shows what percentage of items in the column are valid, have errors, or are empty. If the Valid percentage is not 100, you should investigate the reason, correct the errors, and populate empty values.

• Use a summary table from the data source (if possible)

• Create a data model that is as small as possible, aim for a star schema and review the relationships between your tables to ensure the relationship cardinality properties are correctly configured, e.g. correct direction and type.
  
• Avoid bi-directional cross-filtering relationships unless you fully understand the ramifications of doing so. Enabling it can lead to ambiguity, over-sampling, unexpected results, and performance degradation.
• Disable the auto date/time feature
• Optimize your DAX queries:
  • Filter your data as much as possible before you start performing logic tests (IF, SWITCH, etc.)
  • Keep your DAX function always as simple as possible
  • Consider structuring your conditional logic so that you eliminate most of the rows as early as possible (sometimes this means reversing your logic)
  • Avoid FILTER – use KEEPFILTER instead
  • Never use nested IF statements – use SWITCH instead
  • Try to avoid nested iterators (e.g. SUMX)
  • Avoid multiple measures – use one measure with variables (VAR) instead
  • Avoid the use of EARLIER and EARLIEST – use variables (VAR) instead
  • Try to avoid VALUE and VALUES if not strictly required
  • Avoid error handling like ISERROR – try to ‘catch’ errors before calculations begin
  • Avoid the use of IF and FILTER – move filters to CALCULATE
  • Avoid date functions like LASTDATE – use MIN and MAX instead
  • Do not use math functions like ROUND and FLOOR – take care of this in the ETL process in Power Query
  • Avoid using the DAX functions that are built-in to get quick statistics based on your data, because they are known to cause performance issues. Instead, create the statistical measures yourself by using DAX functions to calculate average, sum, min, max, and so on
  • Avoid calculated tables and columns, because they increase the size of the overall file. Having too many calculated columns will slow performance and will cause you to reach the maximum Power BI data size sooner.

Good luck!