Power BI offers several storage modes that determine how tables are queried. Import mode loads data into the in-memory VertiPaq engine, providing the fastest performance and full DAX support, with data refreshes scheduled separately. DirectQuery leaves data in the source and sends live queries for every visual interaction, ensuring freshness but depending on source performance. Dual mode allows the engine to choose between Import and DirectQuery per query, while Live connections link Power BI to existing Analysis Services or Power BI datasets without allowing model modification. Composite models combine these modes, typically caching smaller dimension tables in Import while querying large fact tables via DirectQuery. Aggregations accelerate DirectQuery performance by routing detail-level queries to pre-summarized Import-mode tables, dramatically reducing response times for billion-row fact tables.
Performance optimization depends on understanding the VertiPaq storage engine and the formula engine. VertiPaq stores data in compressed columnar format using dictionary encoding, so high-cardinality columns consume more memory and slow compression. The formula engine handles complex DAX logic single-threaded, while the storage engine handles simple aggregations in parallel. Best practices include using a star schema, preferring measures over calculated columns, limiting columns and rows imported, setting proper data types, disabling auto-date tables, and reducing high-cardinality visuals. Calculated columns increase model size because they are materialized, so heavy use can slow refresh times. Tools like DAX Studio, Performance Analyzer, and Tabular Editor help diagnose slow measures and inspect query plans.
Row-level security (RLS) restricts data access based on user identity. Roles are defined in Power BI Desktop via the Manage Roles dialog using DAX filter expressions, and users or groups are assigned to those roles in the Power BI Service. Functions such as USERNAME and USERPRINCIPALNAME return the current user's identity and are typically embedded inside RLS filters to dynamically scope data visibility. Other advanced features include bookmarks for capturing report state, drillthrough pages for filtered detail navigation, slicer synchronization across pages, tooltip pages for rich hover details, the Decomposition Tree visual for AI-driven root-cause analysis, and What-If parameters built on GENERATESERIES for scenario modeling. Together, these features support performant, secure, and interactive Power BI solutions.