Pbix file best practices

[julsr]

Hi everyone. 
 
I'm building multiple dashboards that use around 7 tables from our database, so I'm creating multiple measures, calculated columns, and using a lot of Dax. My question is if you guys have any recommendations for storing all the data and calculations inside the report. How should I handle it? What should be the structure inside the pix file?
 
Thanks 

[Ritaf1983 · Accepted solution]

Hi @julsr 

If I understand correctly and the question is about designing the file structure and handling multiple metrics in terms of order, here are two methods I would consider:

1. Create a separate table for metrics and folders by topic. This approach would involve creating a single table at the beginning of the file to define all the measures and then organizing the rest of the file into folders based on different topics. Each folder would contain a table for the metrics related to that topic.

Guide for creating folders :

https://radacad.com/organize-power-bi-dax-measures-in-folders

2. Create a separate table for each group of metrics by topic. This approach would involve creating multiple tables throughout the file, each representing a group of metrics related to a specific topic. The tables would be organized in a way that reflects the hierarchical structure of the topics.

The choice between these two methods depends on personal preference and the specific needs of the project. For a large number of metrics, it may be more convenient to use the second approach, as it can help to break down the data into more manageable chunks. Additionally, documenting the logic behind each metric within its respective table can improve the overall organization and readability of the file.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[julsr]

Great, thanks for sharing this!

Have you seen if moving the measures from the original table to a specific stable improves the speed of the report? Does it have any other advantage besides the organization?

[Ritaf1983]

Hi @julsr 

The "location" of the metric has no impact on its performance; it is purely a cosmetic consideration.

The performance of a Power BI model can be significantly influenced by several characteristics. Here are some key factors:

1. Data Model Complexity:

   • Number of Tables: More tables can increase complexity and affect performance.
   • Relationships: Many-to-many relationships and complex relationship paths can slow down query performance.
   • Table Size: Larger tables with more rows and columns can impact performance.

2. Data Types and Cardinality:

   • High Cardinality Columns: Columns with a high number of unique values (high cardinality) can affect compression and performance.
   • Data Types: Using appropriate data types (e.g., integers instead of strings for keys) can improve performance.

3. DAX Calculations:

   • Calculated Columns: Excessive use of calculated columns can slow down refresh times.
   • Measures: Inefficient DAX measures can slow down report rendering. Use of complex DAX functions and improper context transition can be particularly taxing.

4. Data Refresh:

   • Incremental Refresh: Implementing incremental data refresh can improve performance by reducing the amount of data processed during each refresh.
   • Refresh Frequency: High refresh frequency can strain resources and impact performance.

5. Query Performance:

   • DirectQuery vs. Import Mode: Import mode generally offers better performance than DirectQuery. However, DirectQuery can be useful for real-time data but requires optimized source systems.
   • Query Folding: Ensuring that Power Query transformations can be folded back to the source system can improve performance.

6. Indexes and Keys:

   • Indexes: Proper indexing on source databases can significantly improve DirectQuery performance.
   • Primary and Foreign Keys: Defining keys in the data model helps in optimizing the relationships and joins.

7. Data Compression:

   • Column Storage: Using columnar storage efficiently can improve performance, as Power BI uses a Vertipaq engine that compresses data.

8. Aggregations:

   • Aggregated Tables: Creating aggregated tables for frequently used summaries can speed up queries.

9. Model Size:

   • File Size: Keeping the model size in check by removing unnecessary columns and tables can improve performance.
   • Data Reduction Techniques: Filtering data to include only necessary information helps reduce the model size.

10. Optimization Techniques:

    • Vertipaq Analyzer: Using tools like Vertipaq Analyzer can help in identifying and addressing performance bottlenecks.
    • Performance Analyzer: Utilizing Power BI's Performance Analyzer to diagnose and optimize report performance.
      

      If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly