Skip to content
Data Analytics & BI

Power BI interview questions and answers

64 questions, 25 with code. About 5 h 10 min to read them all.

By TestLab · Updated

Plan for an interview
64 questions

Track what you know and what to revise.

Sign in to track progress

You can open a few full answers without signing in. Sign in to read all

1

Which components make up the Microsoft Power BI platform?

Microsoft Power BI is a business intelligence platform for connecting to data, transforming it with Power Query, modelling relationships and calculations with DAX, and publishing interactive reports, dashboards, and paginated reports. Its main components work together as an end-to-end analytics lifecycle.

Read full answer

Applies toMicrosoft Power BI Desktop (current release) and Power BI Service (current release)

Core Components

Component Purpose Typical use
Power BI Desktop Local development environment for importing data, creating models, writing DAX, and designing reports Build and publish a .pbix report
Power Query Data preparation engine using the M language Clean, reshape, combine, and refresh source data
Data model Related tables containing facts, dimensions, relationships, and calculations Support accurate analysis and efficient reporting
DAX Formula language for calculated columns, measures, and calculated tables Calculate revenue, growth, ratios, and time intelligence
Power BI Service Cloud platform for publishing, sharing, refreshing, and monitoring content Publish reports to workspaces and distribute them as apps
Power BI Mobile Companion application for reviewing content on mobile devices Monitor sales and operational dashboards while travelling
Report Builder Development tool for paginated reports designed for printable or exportable output Produce formal financial and operational reports

Data Preparation with Power Query

Data enters Power BI Desktop through Home > Get Data, where connectors are available for Excel, SQL Server, Azure, SharePoint, Salesforce, Google Analytics, APIs, and many other sources.

The preparation experience is called the Power Query Editor:

  1. Select the source and relevant tables.

  2. Promote the correct headers with Transform > Use First Row as Headers.

  3. Set data types under Transform > Data Type.

  4. Remove errors, unwanted columns, and duplicate records.

  5. Use Merge, Append, Group By, and other transformations to prepare the model.

  6. Choose Close & Apply to load the cleaned data.

Power Query transformations are stored in the report and replayed during refresh. Keeping transformations in Power Query rather than hiding logic in DAX usually makes the model easier to maintain.

Data Modelling and DAX

A common production model is a star schema:

  • A central fact table stores measurable business events.

  • Dimension tables describe dates, products, customers, and other business entities.

  • Many-to-one relationships connect each dimension to the fact table.

  • Relationships normally use single-direction filtering from dimensions to facts.

Measures respond to the report’s filter context, while calculated columns are evaluated for each row. Business values such as revenue, profit, and year-over-year growth should generally be implemented as measures.

A simple measure using a variable is:

Total Revenue = VAR RevenueAmount = SUM ( Sales[Revenue] ) RETURN RevenueAmount

For example:

Category Revenue
Electronics 12,000
Office Supplies 8,000

The Total Revenue measure returns 20,000 for the complete table and 12,000 when the report is filtered to Electronics. Because it is a measure, the same field can provide different values depending on the visual and its filters.

Publishing and Consumption

In Power BI Desktop, select File > Publish and choose a workspace in Power BI Service. Publishing creates a semantic model and its associated report. An imported semantic model contains loaded data, while a DirectQuery semantic model contains the model and connection definitions and queries the source at interaction time.

In the Service:

  • Workspaces organize reports, semantic models, dashboards, and apps.

  • Apps package content for controlled distribution to audiences.

  • Deployment pipelines promote content through development, testing, and production environments.

  • Gateways connect cloud-hosted semantic models to private or on-premises data sources.

  • Row-level security restricts what users can see according to their identity or role.

  • Refresh history and Capacity metrics help administrators monitor reliability and performance.

Individual users generally require an eligible Power BI Pro or Premium Per User licence to publish and consume shared content, while capacity requirements depend on the hosting model used by the organisation.

Best Practices and Common Pitfalls

  • Model deliberately: Use fact and dimension tables instead of one wide, flattened table. Hide technical key columns from report views.

  • Control relationships: Prefer simple many-to-one relationships with single-direction filtering. Use bidirectional filtering only when a specific requirement justifies its performance and ambiguity costs.

  • Optimize refreshes: Apply source-side filtering where supported, remove unnecessary columns, and use incremental refresh for large fact tables.

  • Write readable DAX: Use VAR and RETURN, explicit measure names, and consistent formatting. Avoid measures that repeatedly scan large tables or duplicate complex logic.

  • Design for decisions: Give each page one clear analytical purpose, limit decorative visuals, use consistent colours, provide meaningful labels, and avoid relying on colour alone.

  • Secure before publishing: Test row-level security using Modeling > Manage roles and verify behaviour in the Service under the intended licence and capacity.

2

How do Power BI Desktop, Power BI Service, and Power BI Mobile differ?

Power BI Desktop is the authoring application for connecting to data, modelling it, and building reports. Power BI Service is the cloud platform for publishing, sharing, refreshing, monitoring, and governing content, while Power BI Mobile is primarily used to consume and interact with published content on mobile devices.

Read full answer

Applies toPower BI Desktop, Power BI Service, and Power BI Mobile (current releases)

Core Differences

Capability Power BI Desktop Power BI Service Power BI Mobile
Primary role Author and maintain models and reports Publish, operate, share, and govern assets Consume and interact with published content
Environment Primarily a Windows desktop application Cloud platform accessed through a browser Mobile application for iOS and Android
Data access Connects directly to supported sources and imports or queries data Hosts semantic models and coordinates refreshes Uses content published through Service
Modelling Full authoring of tables, relationships, hierarchies, and DAX Hosts and serves semantic models; editing is primarily performed in Desktop Consumes published semantic models through supported experiences
Refresh Interactive refresh during development Scheduled refresh for supported models, using gateways where required Relies on refreshes performed through Service
Collaboration Local .pbix files, Power BI Project files, and source-control workflows Workspaces, apps, sharing, permissions, deployment pipelines, and monitoring Favorites, alerts, sharing, and mobile-specific interactions
Offline use Available while working locally Normally requires an internet connection Supports limited offline viewing where the relevant content has been downloaded and made available

Power BI Desktop: Authoring

Power BI Desktop supports the complete report-development cycle:

  1. Connect to sources and transform data with Power Query, which uses the M language.

  2. Clean and shape data in the Queries and Data views.

  3. Build a data model, normally using a star schema.

  4. Configure relationships, cardinality, and filter direction.

  5. Create DAX measures, calculated columns, hierarchies, and other model logic.

  6. Design visuals, interactions, and page layouts in the Report view.

  7. Publish the semantic model and report to a Service workspace.

A .pbix file can contain imported data, transformations, the semantic model, DAX, and report visuals. Larger organizations may also use Power BI Project (.pbip) files with source control rather than treating a .pbix file as the only maintainable artifact.

Power BI Service: Publishing and Governance

Publishing from Desktop creates or updates a semantic model and report in the selected workspace. Service provides capabilities for operating that content throughout its lifecycle:

  • Workspaces: Organise content for teams or business units.

  • Apps: Package related reports and dashboards for a defined audience.

  • Sharing and permissions: Control viewing, editing, republishing, and administrative rights.

  • Scheduled refresh: Refreshes supported models using configured credentials and, where needed, an on-premises gateway.

  • Deployment pipelines: Promote content through development, testing, and production where the required capacity is available.

  • Monitoring and governance: Review usage, refresh failures, capacity metrics, lineage, and audit information.

  • Endorsement: Certify selected content as authoritative.

A typical workflow is to develop and validate the model in Desktop, publish it to a development workspace, promote it through deployment pipelines, configure refresh and gateway requirements, and then share it or package it as an app.

Power BI Mobile: Consumption

Power BI Mobile focuses on published content. It supports viewing reports and semantic models, organising favourites, receiving report and data alerts, using touch interactions, and accessing content through links or supported organisation portals.

A desktop report is not automatically effective on a phone. In Desktop, use View > Mobile layout to create a dedicated mobile layout, then resize and reorder important visuals, remove secondary detail, and maintain a sensible reading order. The final layout should be tested in the mobile app on a real device. Offline access is limited and depends on supported download and tenant configurations.

Small Business Example

Suppose a retailer analyses daily sales:

Date Store Sales Amount
2026-01-01 London 1,200
2026-01-01 Manchester 800
2026-01-02 London 1,500

The analyst can create Date and Store dimensions, a Sales fact table, and a Total Sales measure that sums Sales[Sales Amount]. The measure returns 3,500 for all displayed rows, 2,000 for 1 January, and 1,500 for 2 January.

The report is developed in Desktop, validated, published to a Service workspace, and configured for scheduled refresh. Managers can then review it through a browser or Power BI Mobile.

Best Practices and Common Pitfalls

  • Use Desktop for development: Perform data preparation, modelling, DAX, and report design outside production Service content.

  • Apply star-schema modelling: Separate facts and dimensions, use suitable date tables, and avoid unnecessary many-to-many relationships.

  • Prefer measures for business logic: Use measures for calculations that must respond dynamically to filter context; use calculated columns only for genuine row-level logic.

  • Optimize refreshes: Remove unused columns, reduce unnecessary extraction, resolve query-folding issues, and use an appropriate gateway architecture.

  • Design for mobile deliberately: Do not rely on automatic scaling; test the dedicated mobile layout on a real device.

  • Understand ownership: Desktop creates, Service publishes and operates, and Mobile primarily consumes.

3

What are the Power BI Pro, PPU, and Fabric capacity options?

Power BI offers per-user licenses such as Pro and Premium Per User (PPU), plus shared Microsoft Fabric capacities for hosting organizational workloads. The right option depends on whether reports are authored by individuals, shared across teams, governed centrally, or embedded in customer-facing applications.

Read full answer

Applies toPower BI Desktop and Power BI Service (current release), with Microsoft Fabric capacities

Licensing Options

Option Typical cost Hosting model Best suited for
Power BI Free No paid license Shared capacity Personal reports and limited sharing
Power BI Pro US$10 per user/month Shared capacity Most business users who create or share reports
Premium Per User (PPU) US$24 per user/month Individual capacity by default Power users needing personal capacity and isolation
Microsoft Fabric capacity Based on capacity units and active time Dedicated organizational capacity Teams, governance, scale, and predictable shared workloads
Power BI Embedded Separate embedding pricing Embedded capacity or consumption-based model Customer-facing analytics in applications

Prices are shown at the current US list price and can vary by region, agreement, billing period, or Microsoft subscription terms.

  • Power BI Free: Lets an individual create reports in Power BI Desktop and publish to My Workspace. Sharing is limited; Free users generally cannot publish to a shared workspace. Free accounts are not appropriate for broad enterprise collaboration.

  • Power BI Pro: Adds the ability to publish to shared workspaces, share dashboards throughout the organization, and use shared capacity features. A viewer who only has a Free license can generally consume appropriately shared content, but publishing and broad distribution require Pro.

  • Premium Per User (PPU): Provides a user with an individual Fabric-based capacity and the features associated with Premium/PPU, including greater control over publishing and sharing. It is useful where users require a personal capacity boundary rather than relying entirely on a shared organizational capacity.

  • Fabric capacity: Provides a dedicated capacity for multiple workspaces. Power BI report generation, refreshes, and other Fabric workloads consume capacity units. A previously called Premium capacity is now presented as a Fabric capacity.

  • Embedded: Allows application users to interact with Power BI content without receiving individual Power BI licenses. It has its own licensing model and should be considered separately from internal Pro, PPU, and shared-capacity decisions.

How to Configure Capacity

An administrator can purchase and manage shared capacity through the Microsoft Fabric or Azure administrative experience:

  1. Open the admin portal.

  2. Select Capacity or Microsoft Fabric > Capacity.

  3. Choose Add capacity, select the SKU and billing region, and confirm the purchase.

  4. Open the target Power BI workspace.

  5. Select Workspace management > Manage access or Settings > Capacity.

  6. Assign the workspace to the new Fabric capacity.

  7. If users need their own dedicated capacity, assign them through the capacity’s user-assignment settings.

After assignment, monitor utilization with the Fabric Capacity Metrics experience. Capacity can usually be scaled and paused according to Fabric’s current billing rules; pausing it makes the underlying compute unavailable.

Small Business Example

Scenario item Value
Report authors 20
Dashboard viewers 100
Capacity requirement Centralized monitoring and workspace governance

A practical design is:

  • Assign Pro to the 20 authors.

  • Assign the shared workspace to a suitably sized Fabric capacity.

  • Allow the 100 managers to view the reports with existing Free licenses, subject to the company’s sharing and security policies.

  • Use PPU only for users who specifically need individual capacity, isolation, or personal publishing behavior.

The expected result is centralized publishing and capacity management without purchasing a separate paid license for every dashboard viewer.

Deployment and Governance

  • Shared capacity is usually more economical than buying an individual capacity for every report creator.

  • PPU is valuable where workload isolation or personal capacity ownership is a stated requirement, but its higher per-user cost can make it inefficient for a large author population.

  • Fabric capacity supports organizational controls such as workspace deployment pipelines, sensitivity labels, tenant settings, monitoring, and capacity-based workload management.

  • Row-level security restricts what a user sees but does not replace licensing or capacity planning. RLS roles can affect query performance and should be modeled using relationships or explicit DAX logic.

  • Embedded applications require the correct Power BI Embedded licensing arrangement; ordinary Pro licenses are not a substitute for customer-facing embedded rights.

Best Practices and Pitfalls

  • Start with Pro for authors and a shared Fabric capacity for collaboration; use PPU selectively.

  • Size capacity from observed consumption rather than user count alone. Background refreshes, high-cardinality models, excessive visuals, and inefficient DAX can consume substantial capacity.

  • Optimize models with a star schema, active relationships, appropriate date tables, reduced column counts, and efficient measures.

  • Use incremental refresh or other appropriate refresh strategies for large fact tables.

  • Review capacity metrics regularly, but avoid pausing a capacity before scheduled refreshes, semantic model updates, or report distribution have completed.

  • Do not assign viewers a paid Pro license merely to consume dashboards unless sharing policy requires it.

4

What are Power BI’s supported data connectors?

Data connectors define the sources Power BI can ingest, query, refresh, and model. Power BI supports databases, files, cloud platforms, business applications, APIs, and enterprise analytics systems through built-in, AppSource, and generic connectors.

Read full answer

Applies toPower BI Desktop and Power BI Service (current release)

Connector Landscape

Connector availability can differ between Power BI Desktop, Power BI Service, dataflows, Fabric data pipelines, and individual tenants. Use Home > Get Data > More to see the connectors available in the current environment.

Category Representative supported connectors Typical use
Files and folders Excel, CSV, JSON, XML, text, PDF, Parquet, folder Structured extracts and document tables
Databases SQL Server, Azure SQL Database, MySQL, PostgreSQL, Oracle, IBM Db2, Amazon Redshift, Google BigQuery, Snowflake Transactional and analytical workloads
Microsoft platforms SharePoint, SharePoint Folder, Dataverse, Dynamics 365, Microsoft Fabric Lakehouse, Fabric Warehouse, SQL Server Analysis Services Enterprise reporting and Microsoft data estates
SaaS applications Salesforce, Google Analytics, Adobe Analytics, HubSpot, Mailchimp CRM, marketing, web, and customer analytics
Developer and operational systems GitHub, Azure DevOps, Jira, ServiceNow Projects, issues, and operational reporting
Web and generic access Web, OData, ODBC REST APIs and systems without a dedicated connector
Enterprise and legacy systems SAP HANA, SAP Business Warehouse, IBM mainframe databases, and specialist AppSource connectors Centralized and proprietary systems

Some SaaS and operational connectors are published through Microsoft AppSource rather than being built into Power BI Desktop. Their use can depend on tenant policies, authentication requirements, regional availability, and whether the connector supports Desktop, Service, dataflows, or Fabric pipelines.

How Connections Work

A typical Desktop workflow is:

  1. Select Home > Get Data and choose a connector.

  2. Authenticate using a supported method.

  3. Select tables, views, files, or enter a query.

  4. Transform and combine the data in Power Query, which uses the M language.

  5. Select Close & Apply to load it into the model.

The principal storage modes are:

  • Import: Loads data into the semantic model and normally provides the fastest interactive performance.

  • DirectQuery: Sends queries to the source when a visual needs data.

  • Direct Lake: A storage mode optimized for large datasets from supported sources, including Microsoft Fabric Lakehouse and Fabric Warehouse.

Power BI Desktop also provides Live Connect for working with an existing published semantic model rather than importing its data into the local file.

Power BI Service, Fabric, and Gateways

Desktop models are normally published to a workspace in Power BI Service. Additional connectors are not generally added directly through Workspace > New semantic model; for a custom model, data is normally selected and transformed in Desktop before publication. Uploading a supported CSV or Excel file to a workspace creates a new semantic model rather than adding the file to an existing model.

Power BI dataflows and Microsoft Fabric data pipelines can connect supported sources and schedule ingestion into Fabric Lakehouse or Warehouse destinations. Private on-premises sources generally require an on-premises data gateway. A gateway can serve multiple data sources, and gateway clusters provide high availability. Personal Gateway is intended for individual users rather than shared production workloads.

Tenant administrators can control connector use through connector allowlists and related policies in the Microsoft Fabric admin center.

Business Example

Suppose sales.csv contains:

Date Region Net Sales
2025-01-01 North 1,000
2025-01-02 South 800

In Power Query, an analyst selects Home > Get Data > Text/CSV, loads the file, assigns the Date column the Date data type, and names the query Sales. Selecting Close & Apply loads it for modeling. The expected total for the sample is 1,800. The analyst can then relate Sales to separate date and region dimensions to form a star schema.

Best Practices and Pitfalls

  • Prefer Import where appropriate: Imported models generally perform better than DirectQuery, subject to model size and source performance.

  • Filter early: Remove unnecessary rows and columns at the source and preserve source types and keys.

  • Model deliberately: Use a star schema, validate relationship cardinality and filter direction, and avoid unnecessary many-to-many relationships.

  • Protect credentials: Use OAuth, service principals, or managed identities where the connector supports them; never embed secrets in queries or parameters.

  • Design reliable refreshes: Schedule refreshes appropriately, monitor failures and capacity usage, and account for source time zones and service limits.

  • Keep gateway environments separated: Use gateway clusters for availability and follow environment-specific deployment and credential-management policies.

  • Create readable reports: Use meaningful measures, consistent labels, accessible colours, and visuals that directly answer the business question.

5

What is composite model mode in Power BI?

Composite model mode is a Power BI modelling approach that combines tables with different storage modes, usually Import and DirectQuery, in one semantic model. It allows frequently used dimensions to remain fast in VertiPaq while large or frequently changing fact tables are queried directly from their source.

Read full answer

Applies toMicrosoft Power BI Desktop and Power BI Service (current release)

How Composite Models Work

A composite model is not a single on/off switch. You assign a storage mode to individual tables:

Storage mode Behaviour Typical use
Import Data is loaded into Power BI’s in-memory VertiPaq storage Small dimensions, curated reference data and performance-sensitive tables
DirectQuery Visual queries are sent to the underlying data source Large fact tables or data that changes too frequently for scheduled imports
Dual Power BI can use an imported copy or the source, depending on the operation Tables that support more efficient query execution in a composite model

Import tables provide fast filtering and aggregation. DirectQuery tables provide fresher data without loading the complete table into memory. When a visual uses both, Power BI combines their results through the model’s relationships.

This differs from Live Connection: a composite model contains a Power BI semantic model with relationships, measures and DAX, whereas a Live Connection report queries an existing source model directly.

How to Configure It

  1. Load the source data through Home > Get Data.

  2. In the Data pane, select the table that should remain live.

  3. On the Modeling tab, set Storage mode to DirectQuery.

  4. Leave smaller dimension tables as Import.

  5. In Model view, create relationships between the tables, preferably following a star schema.

  6. Publish the report to a Power BI workspace and test the dataset and visuals after publication.

Power Query Editor is used to manage source queries and transformations. Storage mode is configured in Power BI Desktop through the Modeling tab or the table context menu, and it can be verified in the Data pane.

Business Example

Assume the following tables belong to one Power BI model:

Products — Import

ProductID ProductName
P101 Widget
P102 Gadget

Sales — DirectQuery

OrderDate ProductID Amount
2025-01-05 P101 100
2025-01-07 P101 50
2025-01-09 P102 200

Create a one-to-many, single-direction relationship from Products[ProductID] to Sales[ProductID]. Define a measure named Total Sales that sums Sales[Amount].

A matrix using Products[ProductName] and the Total Sales measure produces:

Product Total Sales
Widget 150
Gadget 200
Total 350

The product names come from the imported table, while the sales values are queried from the DirectQuery source. The relationship propagates filters from Products to Sales.

Constraints to Consider

  • DirectQuery performance depends on the source: indexes, partitioning, network latency and query design all affect response time.

  • Query folding matters: the source should perform filtering and aggregation efficiently rather than transferring large intermediate datasets.

  • DirectQuery has modelling restrictions: calculated columns are not supported on DirectQuery tables, and some DAX functions and visual features may also be restricted.

  • Relationships must follow supported rules: relationships between DirectQuery tables from different sources are limited to one-to-many relationships, with the DirectQuery table on the many side.

  • A composite model uses both client-side VertiPaq capacity and source capacity, so source availability and query load must be monitored.

  • DirectQuery is not automatically “real time”: it reflects the source’s current availability, but source-side ETL schedules, caching and latency can still affect freshness.

Best Practices

  • Prefer a star schema, with Import dimensions connected to one or more DirectQuery fact tables.

  • Import dimensions that are relatively small, stable and heavily used for filtering or slicing.

  • Use DirectQuery for genuinely large or frequently changing facts, not simply because it appears more current.

  • Avoid unnecessary bidirectional relationships because they can increase query complexity and ambiguity.

  • Test queries with Performance analyzer and DAX query view, then monitor usage and refresh performance in Power BI Service.

  • Use clear visual encodings and limit the number of DirectQuery-intensive visuals on a page.

  • Keep calculations appropriate to their storage mode: use Import tables for calculated columns, write measures at the model level, and use VAR and RETURN to make complex measure logic readable and efficient.

6

What does Power Query do in Power BI?

Power Query is Power BI’s data preparation and transformation engine. It connects to source data, cleans and reshapes it through an intuitive interface and the M language, then loads the result into a Power BI data model for reporting.

Read full answer

Applies toMicrosoft Power BI Desktop (current release) and Power BI Service (current release)

How It Works

Power Query uses a data preparation workflow:

  1. Select a source from Home > Get Data.

  2. Connect to files, databases, APIs, folders, SharePoint, cloud platforms, and many other systems.

  3. Open Transform Data to launch the Power Query Editor.

  4. Clean, combine, reshape, and validate the data.

  5. Choose Close & Apply to load the query into the Power BI model.

Every action creates an ordered transformation step. The Applied Steps pane shows the complete history, allowing steps to be renamed, reordered, edited, or removed. The underlying M code is available through Advanced Editor.

Power Query mainly performs data engineering tasks:

  • Cleaning: remove errors, blanks, duplicates, and inconsistent formatting.

  • Standardization: correct data types and normalize values.

  • Reshaping: split columns, pivot and unpivot tables, merge tables, and group records.

  • Combining: append multiple datasets or join tables using Table.Combine and Table.Join.

  • Enrichment: add calculated columns or reference related datasets.

  • Automation: use parameters, reusable queries, and scheduled refreshes.

What It Is Used For

Power Query is commonly used to:

  • Prepare sales, finance, marketing, and operational datasets.

  • Combine monthly exports stored in separate files.

  • Flatten nested JSON or XML data.

  • Join customer details to transaction facts.

  • Filter historical data before it enters the model.

  • Create reusable “base” queries and parameterized extracts.

  • Prepare data for Power BI Dataflows, which can be shared across multiple semantic models.

Power Query handles ETL: extract, transform, and load. It generally prepares the structure and values, while DAX calculates dynamic business metrics such as revenue, margin, growth, and year-to-date totals.

Desktop and Service Workflow

In Power BI Desktop, analysts develop queries in the Power Query Editor and publish the resulting semantic model to a workspace in Power BI Service.

In Power BI Service:

  • Scheduled refreshes update semantic models automatically.

  • A gateway connects cloud semantic models to private on-premises data sources.

  • Power BI Dataflows provide governed, reusable datasets in Premium or Fabric capacities.

  • Workspace permissions control who can view or refresh assets.

  • Deployment pipelines promote tested semantic models between development, testing, and production environments.

A query’s connection credentials and refresh schedule are managed separately from its transformation logic.

Business Example

Suppose an orders table contains the following data:

OrderID OrderDate Amount Status
1001 2025-01-05 125.50 Paid
1002 2025-01-06 80.00 Pending
1003 2025-01-07 42.00 Cancelled

The analyst needs to exclude cancelled orders and calculate AmountInclTax at 20%.

In Power Query Editor:

  1. Select the Status column, open its filter menu, and uncheck Cancelled.

  2. Select Add Column > Custom Column.

  3. Name the column AmountInclTax.

  4. Enter the formula [Amount] * 1.2.

The expected result is:

OrderID OrderDate Amount Status AmountInclTax
1001 2025-01-05 125.50 Paid 150.60
1002 2025-01-06 80.00 Pending 96.00

Best Practices

  • Preserve source data: Perform destructive transformations in separate queries so original fields remain available when requirements change.

  • Use explicit data types: Set dates, numbers, and text columns deliberately rather than relying entirely on inferred types.

  • Avoid unnecessary columns early: Removing unused columns can improve processing and reduce model size.

  • Reuse queries: Create a base query and use Reference for downstream transformations instead of duplicating logic.

  • Use parameters: Centralize file paths, URLs, database names, and date filters.

  • Check query folding: Power Query attempts to push transformations into sources such as SQL Server. Folding improves performance, but some transformations prevent it.

  • Keep responsibilities clear: Use Power Query for ETL and DAX for measures that must respond dynamically to the visual’s filter context.

  • Model for analysis: Load clean queries into a star schema with explicit relationships and appropriate cardinality; define relationships in Model view, not in M.

  • Test refresh behavior: Validate credentials, gateway availability, source limits, incremental refresh settings, and expected row counts before production deployment.

7

What is the M language in Power BI?

M is the formula language used by Power Query to transform, combine, and clean data before it enters a Power BI data model. Power Query automatically generates M expressions for many user actions, but experienced analysts also write them directly in the Advanced Editor for reusable and controlled data preparation.

Read full answer

Applies toMicrosoft Power BI Desktop and Power BI Service (current release)

How M Works

Power BI Desktop runs M expressions through the Power Query engine. Each query represents a transformation pipeline:

  1. A source such as Excel, SQL Server, SharePoint, or an API is referenced.

  2. M functions filter rows, rename columns, change data types, join tables, and calculate values.

  3. The final result is loaded into the data model or referenced by another query.

  4. The same query logic can be reused during scheduled refreshes in Power BI Service.

Unlike DAX, which primarily performs calculations within the semantic model, M prepares the source data. M expressions are generally immutable: functions return a new value rather than modifying the previous result.

Core Language Constructs

  • let and in: Define named intermediate steps and return the final result.

  • Functions: Perform transformations, such as Table.SelectRows, Table.AddColumn, and Table.NestedJoin.

  • Records: Structured key-value values written as [Field = Value].

  • Lists: Ordered collections written as {Value1, Value2}.

  • Tables: Structured data containing rows and typed columns.

  • Optional values: A value can be supplied or omitted when a function declares an optional parameter.

  • Data types: Explicit types such as number, text, date, and Int64.Type control how values are stored and interpreted.

M function and identifier names are case-sensitive. A useful query usually assigns each meaningful operation to a descriptive variable rather than constructing one long expression.

Business Example

Suppose an online-sales source contains the following records:

OrderID Product OrderDate Amount
1 Widget 2025-01-10 100
2 Gadget 2025-01-12 50
3 Widget 2025-02-08 120

The business requirement is to standardize the date type, identify the month for reporting, and classify orders with revenue of at least 100 as High.

In Home > Transform data, open the relevant query and select Advanced Editor. Within a let expression, use Table.TransformColumnTypes to assign explicit column types. Use Table.AddColumn with Date.StartOfMonth to create MonthStart, and a second Table.AddColumn step to create RevenueBand. Finish with Table.Sort to order the records by OrderDate, and return the final table after in.

The expected result is:

OrderID Product OrderDate Amount MonthStart RevenueBand
1 Widget 2025-01-10 100 2025-01-01 High
2 Gadget 2025-01-12 50 2025-01-01 Standard
3 Widget 2025-02-08 120 2025-02-01 High

Best Practices and Power BI Integration

  • Set types early: Apply Table.TransformColumnTypes near the source so downstream transformations operate predictably.

  • Preserve query folding: Prefer source-side filtering and projection where the connector supports them. Review the generated native query to confirm unnecessary rows and columns are not being retrieved.

  • Build modular queries: Reference a cleaned staging query instead of repeating its transformations in several downstream queries.

  • Control loading: Disable load for intermediate queries that only support another query, then reference them through the Manage Queries > Reference workflow.

  • Use parameters for changing inputs: Centralize server names, folder paths, and date ranges instead of editing multiple queries.

  • Avoid unnecessary columns: Remove unused fields before expensive transformations when doing so does not break required business logic.

  • Handle errors deliberately: Use Table.ReplaceErrors only when an appropriate fallback exists; do not silently discard data-quality problems.

  • Separate responsibilities: Use M for source preparation, a star schema for modelling, and DAX measures for report-level business calculations.

  • Test refresh behavior: For service refreshes, configure the appropriate Power BI Gateway, credentials, and source permissions before publishing the dataset.

8

How to remove duplicates, split columns, and change types in Power Query

Use Power Query transformations to clean data before it enters the Power BI data model. Deduplicate rows using the appropriate business key, split text columns with a delimiter or other splitter, and explicitly assign data types so downstream measures and relationships behave predictably.

Read full answer

Applies toPower BI Desktop (current release) and Power BI Service (current release)

Step-by-Step Workflow

  1. Select the source table in Power BI Desktop and choose Home > Transform Data to open Power Query Editor.

  2. To remove duplicates:

  3. Select the columns that identify duplicates, such as Order ID.

  4. Choose Home > Remove Rows > Remove Duplicates.

  5. Keep the first occurrence of each selected key.

  6. To split a column:

  7. Select the text column, such as Customer.

  8. Choose Home > Split Column > By Delimiter.

  9. Select the delimiter, configure the output, and choose OK.

  10. Other options include By Character, By Position, By Digits, By Transitions and By Pattern.

  11. To change data types:

  12. Select one or more columns.

  13. Choose Transform > Data Type.

  14. Assign types such as Whole Number, Decimal Number, Currency, Date, Date & Time or Text.

  15. Choose Close & Apply to update the Power BI model.

How the Transformations Work

  • Duplicate removal: Table.Deduplicate removes rows using the specified key columns. Removing duplicates from the entire row table treats only completely identical rows as duplicates.

  • Column splitting: Table.SplitColumn applies an M Splitter to a text column. For example, Splitter.SplitTextByDelimiter separates values using a delimiter such as a comma or space.

  • Data type conversion: Table.TransformColumnTypes applies explicit schema types. This is generally preferable to relying on Power BI’s inferred types because source files and database queries can change unexpectedly.

  • Evaluation order: Power Query evaluates steps from top to bottom. The order can affect results—for example, converting numeric columns before removing invalid rows.

Sample Data and Expected Result

Source data:

Order ID Customer Region Amount
1001 Ava Chen East 25.50
1001 Ava Chen East 25.50
1002 Liam Patel West 40.00

After deduplicating on Order ID, splitting Customer by a space and changing the numeric types:

Order ID First Name Last Name Region Amount
1001 Ava Chen East 25.50
1002 Liam Patel West 40.00

Code Example

The following M script performs all three transformations. Open View > Advanced Editor in Power Query Editor to add or edit the steps.

let
    Source = Table.FromRecords({
        [Order ID = "1001", Customer = "Ava Chen", Region = "East", Amount = "25.50"],
        [Order ID = "1001", Customer = "Ava Chen", Region = "East", Amount = "25.50"],
        [Order ID = "1002", Customer = "Liam Patel", Region = "West", Amount = "40.00"]
    }),
    Typed = Table.TransformColumnTypes(
        Source,
        {
            {"Order ID", Int64.Type},
            {"Customer", type text},
            {"Region", type text},
            {"Amount", Currency.Type}
        }
    ),
    Deduplicated = Table.Deduplicate(Typed, {"Order ID"}),
    SplitNames = Table.SplitColumn(
        Deduplicated,
        "Customer",
        Splitter.SplitTextByDelimiter(" ", QuoteStyle.None),
        "Customer Tokens"
    ),
    Renamed = Table.RenameColumns(
        SplitNames,
        {
            {"Customer Tokens.1", "First Name"},
            {"Customer Tokens.2", "Last Name"}
        }
    )
in
    Renamed

Best Practices and Common Pitfalls

  • Deduplicate on business keys: Use identifiers such as Customer ID or Order ID, not names alone. This prevents distinct records with similar names from being removed.

  • Clean dimensions before modelling: Duplicate dimension keys can create ambiguous relationships. Clean and conform dimension tables in Power Query before defining relationships.

  • Type columns explicitly: Apply stable types such as Int64.Type, Currency.Type and type text. Keep identifiers as text when they are labels rather than quantities.

  • Control invalid values: Decide whether bad dates or amounts should produce errors, become null, or be moved to a rejected-data query. Do not silently discard important records without a documented rule.

  • Check the locale: Date and decimal parsing can depend on source and Power Query locale settings.

  • Protect performance: Perform transformations in the source query rather than with repeated DAX calculated columns. However, check query folding in the current source because unsupported operations may require the source to be evaluated first.

  • Plan for refresh: These transformations run again whenever the model refreshes. Validate source uniqueness, publish the updated query through your normal deployment process, and ensure the relevant Power BI gateway has current source credentials.

9

How do merge and append queries differ in Power Query?

A merge query joins tables by matching key columns and adding columns from a lookup, while an append query stacks tables vertically by adding rows. Merge is used for enrichment, such as adding customer details to transactions; append is used for combining datasets with the same schema, such as monthly sales extracts.

Read full answer

Applies toPower BI Desktop (current release)

Core Difference

Operation Direction How columns are aligned Typical use
Merge Horizontal—adds columns Rows match using one or more key columns; the join type determines how unmatched rows are handled Add customer, product, or date attributes to a fact table
Append Vertical—adds rows Columns are aligned by position, not matched by header name Combine monthly files, regional extracts, or historical snapshots

Both operations are available in Power Query Editor under Home > Combine. Append does not match records or remove duplicates; it simply combines the rows.

Merge Example

Suppose Sales contains:

OrderID Product Amount
1001 Widget 100
1002 Gadget 80

And Customers contains:

OrderID Customer
1001 Alice
1002 Bob

A left outer merge on OrderID produces:

OrderID Product Amount Customer
1001 Widget 100 Alice
1002 Gadget 80 Bob

To perform the merge:

  1. Open Power Query Editor.

  2. Select Home > Combine > Merge Queries > Merge Queries as new.

  3. Choose Sales as the primary table.

  4. Choose Customers as the table to merge.

  5. Select OrderID in both tables.

  6. Choose Left Outer and select OK.

  7. Expand the Customers column and retain only the required fields.

Common join types include Inner, Left Outer, Right Outer, Full Outer, Left Anti, and Right Anti.

Append Example

Suppose Sales2025 contains:

OrderID Product Amount
1001 Widget 100

And Sales2026 contains:

OrderID Product Amount
1002 Gadget 80

Appending them produces:

OrderID Product Amount
1001 Widget 100
1002 Gadget 80

To perform the append:

  1. Select the first query in Power Query Editor.

  2. Select Home > Combine > Append Queries > Append Queries as new.

  3. Add Sales2026 as the table to append.

  4. Select OK.

Before appending, ensure both tables have the same number of columns in the same order and that corresponding columns have compatible data types. If a column is missing, has a different name, or appears in a different position, add, rename, or reorder it first to prevent values from being placed under the wrong headings.

Best Practices and Common Pitfalls

  • Validate merge keys: Keep one matching row per key in the lookup table where possible. Duplicate lookup rows can multiply fact rows and distort measures.

  • Investigate unmatched records: Use Left Anti or Right Anti to identify rows that have no match.

  • Build a star schema: Load fact and dimension queries separately, then create relationships in the Power BI model. Avoid flattening dimensions into facts unless a report specifically requires it.

  • Standardize appended schemas: Align column order, names, data types, currencies, units, and reporting periods before combining data.

  • Expand only necessary columns: Extra columns increase model size and can make reports harder to maintain.

  • Plan refreshes efficiently: Apply filtering and aggregation upstream where appropriate, and avoid repeating expensive transformations unnecessarily.

  • Design for clarity: Use merge for descriptive lookup context and append for additive datasets; choose visuals and labels that make the reporting purpose clear.

10

Why should Power BI users understand query folding?

Query folding is a Power Query optimization that pushes eligible transformations, such as filters and column selection, into the source system instead of retrieving the entire source table first. Understanding it helps users reduce refresh time and network traffic, diagnose slow refreshes, and avoid transformations that cause large datasets to be processed locally.

Read full answer

Applies toPower BI Desktop (current release) and Power BI Service in Microsoft Fabric (current release)

How It Works

Power Query asks the connector to translate supported transformations into a source-native query. For example, when the source is SQL Server, a Power BI filter can become a SQL WHERE clause.

The general flow is:

  1. Power Query evaluates the applied transformations.

  2. The connector generates a source-native query where folding is supported.

  3. The source performs the supported operations.

  4. Power BI receives the reduced result set.

Depending on the connector and source, operations such as filtering rows, selecting or removing columns, renaming columns, sorting, and aggregating can fold. The corresponding M transformations include Table.SelectRows, Table.SelectColumns, Table.RemoveColumns, and Table.RenameColumns, but their presence does not by itself guarantee folding; step order and connector capabilities also matter.

Some transformations cannot be translated into the source query. Table.Buffer stops folding at that point, while complex custom functions and merges often prevent full folding. Power Query must then retrieve the required intermediate data and perform those operations locally.

Query folding is different from DirectQuery. Folding is primarily a query-translation and data-retrieval optimization, whereas DirectQuery changes when report queries are evaluated against the source.

Business Example

Suppose a SQL Server table contains these sales records:

SaleID ProductID SaleDate Amount
1 101 2025-01-05 100
2 102 2025-01-06 50
3 101 2025-01-07 200
4 103 2025-01-08 80

The analyst needs sales with an Amount of at least 100.

SaleID ProductID SaleDate Amount
1 101 2025-01-05 100
3 101 2025-01-07 200

Only two rows are required, although the source table contains four. With full folding, SQL Server performs the filter before transferring the rows. The output order is not guaranteed unless an explicit sort is applied.

How to Verify Folding

Use the following checks in Power BI Desktop:

  1. Open the affected query in Power Query Editor.

  2. Right-click the query and select View Native Query, if the connector provides it.

  3. Confirm that the filter appears in the native query. For the example above, look for a condition equivalent to WHERE [Amount] >= 100.

  4. For detailed refresh timing, select View > Query Diagnostics, start diagnostics, refresh the data, and inspect the generated queries and source execution times.

The available diagnostics depend on the connector. A connector may support folding for some operations but not others, so each important query should be checked rather than assumed to fold.

Performance and Data Modelling Impact

  • Faster refresh: Folded filters and column projections can reduce the amount of data that must be extracted and loaded.

  • Lower memory use: Power BI can store and process fewer rows and columns when the relevant transformations fold.

  • Less network and gateway overhead: Fewer rows and columns cross the network and require less gateway processing.

  • Better scalability: The benefit becomes more significant as source tables grow.

  • Source workload still matters: Folding can move filtering and aggregation work into the source, so it does not necessarily reduce the source’s CPU or execution-plan cost.

Folding does not replace good data modelling. Use a star schema with a fact table and conformed dimension tables, and avoid performing joins in Power Query when those relationships can be represented as Power BI model relationships. Folding should improve extraction without distorting the model’s required grain.

Best Practices and Pitfalls

  • Filter early: Apply selective source-side filters before adding non-foldable steps.

  • Select required columns: Avoid importing unused fields, especially wide transactional tables.

  • Inspect generated queries: Do not assume a transformation folds because the refresh succeeds.

  • Avoid Table.Buffer: It deliberately stops further folding and can increase memory consumption.

  • Treat merges carefully: They often require larger datasets to be retrieved for local processing; use model relationships where possible.

  • Avoid excessive custom M functions: Nested functions and row-by-row logic frequently break folding.

  • Validate source capacity: Review query execution plans and ensure relevant filters and projected columns are indexed appropriately.

  • Do not pre-aggregate facts unnecessarily: Source-side aggregation can improve extraction but may remove transaction-level detail required by the model.

11

How do you unpivot columns into rows in Power Query?

Unpivot converts a wide table into a long format by turning selected columns into rows, with one column identifying the original column and another storing its value. In Power BI, this is commonly used to prepare a fact table for more flexible reporting.

Read full answer

Applies toPower BI Desktop (current release) with Power Query (M), including Power BI Service dataset refresh

What Unpivot Does

Suppose a sales table contains separate columns for revenue and cost:

Country Product Revenue Cost
France Product A 100 60
France Product B 120 70

After unpivoting Revenue and Cost, the data becomes:

Country Product Metric Amount
France Product A Revenue 100
France Product A Cost 60
France Product B Revenue 120
France Product B Cost 70

This long format makes it easier to filter and report by metric. Metric-specific measures can calculate from the same Amount column rather than referencing separate wide-table columns.

Unpivot in Power Query Editor

  1. Open the source query in Power Query Editor.

  2. Select the columns to unpivot, such as Revenue and Cost.

  3. On the Transform tab, select Unpivot Columns.

  4. Power Query creates two output columns, normally named Attribute and Value.

  5. Rename them to meaningful names such as Metric and Amount.

  6. Select Close & Apply.

If the table contains many metric columns, select the columns that should remain unchanged, such as Country and Product, and choose Transform > Unpivot Other Columns.

Use Unpivot Other Columns carefully. It unpivots every column except the selected ones, so an unexpected descriptive or identifier column could also be converted into rows.

Modelling the Unpivoted Data

Load the unpivoted table as a fact table, for example named Fact. The Metric column can be used in slicers or matrix columns, while DAX measures filter Fact[Amount] by the required metric.

For the sample data, a Revenue measure returns:

Product Revenue
Product A 100
Product B 120
Total 220

A corresponding Cost measure returns 130 overall. Use measures rather than calculated columns for values that should respond dynamically to slicers, filters, and visual interactions.

Code Example

The explicit Power Query operation is:

let
    Source = Table.FromRecords({
        [Country = "France", Product = "Product A", Revenue = 100, Cost = 60],
        [Country = "France", Product = "Product B", Revenue = 120, Cost = 70]
    }),
    Unpivoted = Table.Unpivot(
        Source,
        {"Revenue", "Cost"},
        "Metric",
        "Amount"
    )
in
    Unpivoted

For a changing source where all non-key columns should be unpivoted, use the complete operation below; in a production query, Source would normally reference the upstream source query:

let
    Source = Table.FromRecords({
        [Country = "France", Product = "Product A", Revenue = 100, Cost = 60],
        [Country = "France", Product = "Product B", Revenue = 120, Cost = 70]
    }),
    Unpivoted = Table.UnpivotOtherColumns(
        Source,
        {"Country", "Product"},
        "Metric",
        "Amount"
    )
in
    Unpivoted

Table.Unpivot is generally safer when the metric columns are known and controlled. Table.UnpivotOtherColumns is useful for dynamic sources, but its output changes if the source schema changes.

The measures can be defined with valid, readable DAX:

Revenue =
VAR RevenueAmount =
    CALCULATE(
        SUM(Fact[Amount]),
        Fact[Metric] = "Revenue"
    )
RETURN
    RevenueAmount

Cost =
VAR CostAmount =
    CALCULATE(
        SUM(Fact[Amount]),
        Fact[Metric] = "Cost"
    )
RETURN
    CostAmount

Best Practices and Common Pitfalls

  • Unpivot early: Normalize wide business data before building the star schema and measures.

  • Keep identifiers in dimensions: Move attributes such as Country, Product, and Date into related dimension tables where appropriate.

  • Check data types: Confirm that the unpivoted value column remains numeric; set the type explicitly if necessary.

  • Avoid accidental unpivoting: Inspect the selected columns before using Unpivot Other Columns.

  • Remove empty or invalid rows: Filter blank metric names and values after unpivoting.

  • Preserve query folding: Apply transformations close to the source and avoid steps that prevent folding when working with large relational or cloud datasets.

  • Refresh carefully: Test the query after source columns are added, renamed, or removed, especially when using dynamic unpivoting.

  • Design reports from the long model: Use the Metric field for slicers or matrix columns and measures such as Revenue for reliable totals.

12

How should Power Query errors and null values be handled?

Power Query handles errors through try...otherwise, Table.ReplaceErrors, or column error commands, while null values are valid M values that require explicit fallback logic. The appropriate approach depends on whether the source contains an invalid conversion, a missing value, or an intentionally empty string.

Read full answer

Applies toPower BI Desktop (current release) and Power BI Service (current release)

Key Distinctions

  • Error value: An operation could not be completed, such as converting "N/A" to a number. Errors can propagate through later transformations unless handled.

  • Null value: A missing M value, commonly representing a database NULL. It is not an error and does not prevent a query from running.

  • Empty string: A zero-length text value (""). It is different from null, although some connectors and Power Query interface labels can make the distinction less obvious.

  • Blank or empty source field: Inspect the underlying value and data type before deciding how to handle it.

Error-Handling Approaches

  • try...otherwise: Catches an error in a single expression and returns a fallback:

m SafeAmount = (value as any) => try Number.FromText(Text.From(value)) otherwise null

Without otherwise, the expression returns an error. The fallback can be null, zero, a default value, or Error.Record(...) when structured error information is required.

  • Table.ReplaceErrors: Applies one consistent replacement to errors in selected columns across a table and avoids duplicating the fallback expression across custom columns. Its performance depends on the source and query plan, so it should not automatically be assumed to outperform try...otherwise.

  • Column commands: Right-click a column header and choose Remove Errors, Keep Errors, or Replace Errors:

  • Remove Errors deletes rows containing errors.

  • Keep Errors preserves them for investigation.

  • Replace Errors substitutes another value.

  • Column profile: Select View > Column profile > Column statistics to inspect valid values, errors, empty values, blanks, distinct counts, and minimum and maximum values before cleaning the data.

Null-Handling Approaches

  • Use Table.ReplaceValue to replace null with a meaningful fallback.

  • Use a custom column with try...otherwise when the fallback depends on row-level logic.

  • Use an explicit status or quality flag when missing and invalid values must be reported differently.

  • Use Value.Is([Column], Value.Type.Null) to detect a null M value. Test for an empty string separately with [Column] = "".

  • Avoid replacing every null with zero automatically. Zero can be a valid business value and may misstate missing revenue, quantity, or totals.

Business Example

Consider this source data:

OrderID Customer Amount
1001 Northwind 120.50
1002 null null
1003 Contoso "N/A"

The expected cleaned result is:

OrderID Customer Amount AmountStatus
1001 Northwind 120.50 Valid
1002 (Unknown) 0 Missing/invalid
1003 Contoso 0 Missing/invalid

The status field distinguishes valid numeric values from values defaulted because Amount was null or could not be converted.

Code Example

let
    Source = Table.FromRecords({
        [OrderID = 1001, Customer = "Northwind", Amount = 120.50],
        [OrderID = 1002, Customer = null, Amount = null],
        [OrderID = 1003, Customer = "Contoso", Amount = "N/A"]
    }),

    // Convert Amount to a number; invalid values become cell-level errors.
    TypedAmount = Table.TransformColumnTypes(
        Source,
        {{"Amount", type number}}
    ),

    // Replace conversion errors with null.
    ErrorsAsNull = Table.ReplaceErrors(
        TypedAmount,
        each [Error] = null,
        {"Amount"}
    ),

    // Record whether Amount contains a non-null value after error replacement.
    WithAmountFlag = Table.AddColumn(
        ErrorsAsNull,
        "HasAmount",
        each not Value.Is([Amount], Value.Type.Null),
        type logical
    ),

    // Supply business defaults.
    AmountWithDefault = Table.ReplaceValue(
        WithAmountFlag,
        "Amount",
        null,
        0,
        Replacer.ReplaceValue,
        {"Amount"}
    ),

    CustomerWithDefault = Table.ReplaceValue(
        AmountWithDefault,
        "Customer",
        null,
        "(Unknown)",
        Replacer.ReplaceValue,
        {"Customer"}
    ),

    WithStatus = Table.AddColumn(
        CustomerWithDefault,
        "AmountStatus",
        each if [HasAmount] then "Valid" else "Missing/invalid",
        type text
    ),

    Result = Table.SelectColumns(
        WithStatus,
        {"OrderID", "Customer", "Amount", "AmountStatus"}
    )
in
    Result

Best Practices and Common Pitfalls

  • Profile before cleaning: Identify whether an issue is a conversion error, null, empty string, or an upstream source problem.

  • Retain raw data: Use a staging query for exploration and a separate cleaned query for the Power BI model.

  • Avoid blanket error removal: Removing rows can silently change totals and business metrics.

  • Prefer query folding: Perform supported transformations close to the source to reduce refresh cost.

  • Check refresh impact: A query-level error can block a dataset refresh in Power BI Service, while handled cell-level errors should not.

  • Model missing dimension keys explicitly: Create an Unknown or Not Applicable member instead of relying on null dimension keys.

  • Document defaults: Make it clear whether zero means unknown, not applicable, or an actual zero.

13

How do parameters work in Power Query?

A Power Query parameter is a named value stored as a separate query and reused by dependent queries. It lets analysts configure inputs such as a file path, reporting period, sales threshold, or API endpoint without rewriting each query that uses the value.

Read full answer

Applies toPower BI Desktop (current release) and Power BI Service (current release)

How Parameters Work

A parameter is a query that returns a single value. Other queries reference it by its query name, so changing the parameter and refreshing its consumers changes their results. Parameter values are used when queries execute; they do not respond directly to interactions with report visuals.

A Power Query parameter differs from a Power BI report parameter:

Feature Evaluation point Typical use
Power Query parameter Query execution or dataset refresh Source path, date range, threshold
Power BI report parameter When a visual evaluates the selected measure Measure switching; not visual filtering

Creating and Using a Parameter

  1. In Power BI Desktop, open Power Query Editor.

  2. Select Home > Manage Parameters > New Parameter.

  3. Enter a descriptive name, such as MinimumOrderAmount.

  4. Select the data type, such as Number, Text, or Date.

  5. Enter the current value and select OK.

  6. Reference MinimumOrderAmount from dependent queries using the Name option in the formula bar.

To change the value, select Home > Manage Parameters > Edit. Refresh the dependent query if it is not refreshed automatically. In Power BI Service, the parameter is stored in the semantic model, and a changed value affects subsequent dataset refreshes.

Business Example

Suppose an Orders query contains:

OrderID OrderDate Amount
1001 2025-01-05 120
1002 2025-01-07 80
1003 2025-02-02 150

Create MinimumOrderAmount with a current value of 100. Filtering orders where Amount is greater than or equal to the parameter produces:

OrderID OrderDate Amount
1001 2025-01-05 120
1003 2025-02-02 150

Changing the parameter to 130 leaves only order 1003 after the dependent query is refreshed.

Code Example

The parameter query includes metadata that identifies it as a parameter:

// Parameter query named MinimumOrderAmount
let
    Source = 100
in
    Source
meta [
    IsParameterQuery = true,
    Type = "Number",
    IsParameterQueryRequired = true
]

A dependent query references the parameter by its query name:

let
    Orders = Table.FromRecords({
        [OrderID = 1001, OrderDate = #date(2025, 1, 5), Amount = 120],
        [OrderID = 1002, OrderDate = #date(2025, 1, 7), Amount = 80],
        [OrderID = 1003, OrderDate = #date(2025, 2, 2), Amount = 150]
    }),
    TypedOrders = Table.TransformColumnTypes(
        Orders,
        {
            {"OrderDate", type date},
            {"Amount", type number}
        }
    ),
    // Read the value from the separate parameter query
    MinimumAmount = MinimumOrderAmount,
    FilteredOrders = Table.SelectRows(
        TypedOrders,
        each [Amount] >= MinimumAmount
    )
in
    FilteredOrders

Best Practices and Limitations

  • Use descriptive names such as SalesFilePath instead of Parameter1.

  • Set the correct data type so comparisons, filters, and date operations behave predictably.

  • Hide parameter queries from report views when possible; do not create relationships between parameter queries and fact or dimension tables.

  • Changing a parameter in Power BI Service does not by itself refresh downstream queries; trigger and monitor the appropriate dataset refresh.

  • Use DAX measures and slicers for interactive filtering. Use report parameters only for measure switching.

  • Do not store passwords or access tokens directly in a parameter query because its value can be exposed with the model. Use connector-supported mechanisms such as service principals, managed gateway credentials, or Key Vault where available.

15

What are fact and dimension tables in Power BI?

A fact table contains measurable business values at a defined grain, while a dimension table contains the descriptive attributes used to analyze those facts. In Power BI, they are normally connected in a star schema, allowing dimension attributes to filter measures calculated from fact data.

Read full answer

Applies toPower BI Desktop (current release) and Power BI Service (current release)

Core Difference

  • Fact table: Stores numeric fact values, foreign keys, and other fields associated with business events. Its grain should be clearly defined, such as one row per sales transaction, order, invoice, or monthly account balance.

  • Dimension table: Stores descriptive attributes. Typical examples include Dim Product, Dim Customer, Dim Date, and Dim Geography.

  • Measure: A DAX calculation that aggregates data, usually from fact-table columns. Examples include total sales, profit, and order count.

  • Calculated column: A row-level DAX calculation evaluated during data refresh and stored in the model. It is useful for row-level logic, but measures are generally preferred for aggregations because they avoid storing calculated results for every row.

Star Schema Example

Suppose a retailer has these tables:

Fact Sales

SalesOrderID ProductKey OrderDateKey Amount
1001 201 202401 100.00
1002 202 202401 50.00
1003 201 202402 25.00

Dim Product

ProductKey ProductName Category
201 Wireless Mouse Electronics
202 Desk Lamp Home Office

The relationship would normally connect Dim Product[ProductKey] on the one side to Fact Sales[ProductKey] on the many side.

  • Cardinality: One product can appear in many sales rows, so this is a one-to-many relationship.

  • Filter direction: The relationship should normally be single-directional from Dim Product to Fact Sales, allowing product attributes to filter sales measures.

  • Grain: Fact Sales contains one row per sales transaction.

  • Measures: Numeric columns such as Amount provide the source values aggregated by DAX measures.

DAX Example

The corresponding measures can be defined as Total Sales Amount = SUM ( 'Fact Sales'[Amount] ) and Electronics Sales Amount = CALCULATE ( [Total Sales Amount], KEEPFILTERS ( 'Dim Product'[Category] = "Electronics" ) ).

Expected results for the sample data are:

Measure Result
[Total Sales Amount] 175.00
[Electronics Sales Amount] 125.00

CALCULATE evaluates [Total Sales Amount] in a modified filter context. KEEPFILTERS retains existing filters on the specified column when the Electronics filter is applied, while other report filters, such as a selected month, remain in effect.

Data Modelling Best Practices

  • Use one clear grain per fact table: State the business meaning of a row before creating measures.

  • Prefer star schemas: A central fact table with related dimension tables is easier to navigate and usually performs better than a wide, highly denormalized table.

  • Reuse conformed dimensions: If several fact tables use customers, products, or dates, model shared dimensions consistently where the business definitions match.

  • Avoid unnecessary many-to-many relationships: They add complexity to filter propagation and can produce ambiguous results.

  • Use measures for aggregation: Define revenue, margin, and order counts as measures unless row-level calculation is specifically required.

  • Set explicit data types: Ensure both sides of a relationship use compatible key types, dates use the date type, and decimal columns have appropriate precision and scale.

  • Avoid relationships based only on row position: Create relationships using matching key columns and test them in Model view.

Visual Design Guidance

  • Use dimension fields for slicers, hierarchies, labels, and drill-down paths, such as Category, ProductName, and City.

  • Use measures for card values, visual values, tooltips, and conditional formatting, such as [Total Sales Amount] and [Profit].

  • Keep each visual focused on one analytical question. For example, use a clustered column chart for sales by category and a table for product-level details.

  • Display the measure’s unit and time period clearly, and test visuals with filters and slicers to verify that relationship direction and filter context behave as intended.

16

What is a relationship in Power BI?

A relationship is a model-level connection between columns in two Power BI tables. It enables filter propagation and related lookups, allowing dimension tables to filter fact tables and measures to retrieve dimension attributes.

Read full answer

Applies toPower BI Desktop and Power BI Service (current release)

How Relationships Work

Power BI relationships are defined in the semantic model rather than by merging queries in Power Query. A typical star schema contains:

  • Dimension tables: Products, customers, dates, or other descriptive attributes.

  • Fact tables: Sales, orders, transactions, or other numeric events.

  • Relationships: Links from dimension keys to fact-table keys.

For example, Product[ProductKey] can relate to Sales[ProductKey]. When a user selects a product or category in a visual, the filter travels through the relationship and restricts the relevant sales records.

Relationships also support DAX functions such as RELATED and RELATEDTABLE, which retrieve data from the table on the other side of a relationship.

When a measure such as the sum of Sales[Amount] is evaluated with CALCULATE filtering Product[Category] to "Accessories", the active relationship propagates that filter to Sales, and the measure returns 450.

Creating and Configuring a Relationship

Use either method:

  1. Open Model view.

  2. Drag a column from one table onto the matching key column in another table.

  3. Select the relationship line to open its properties pane.

Alternatively, select Home > Manage relationships > New and specify the tables, columns, cardinality, and filter direction.

The main properties are:

Property Meaning Recommended use
Name Identifier for the relationship. Use a descriptive name when several relationships exist.
From table and column Source side of the connection. Usually the dimension table.
To table and column Destination side of the connection. Usually the fact table.
Cardinality Number of matching rows on each side. Prefer one-to-many from dimension to fact.
Cross-filter direction Direction in which filters propagate. Keep single direction unless bidirectional filtering is necessary.
Active or inactive Determines whether the relationship propagates filters and can be used directly. Keep standard reporting relationships active.
Assume referential integrity Indicates that every fact key should match a dimension key. Enable when the data model guarantees this condition.

Common cardinality options include one-to-many, many-to-one, one-to-one, and many-to-many. The displayed direction depends on the selected From and To tables.

Worked Example

Consider these small tables:

Product

ProductKey Product Category
1 Keyboard Accessories
2 Monitor Electronics
3 Mouse Accessories

Sales

SaleID ProductKey Amount
101 1 100
102 2 500
103 1 150
104 3 200

Create an active, single-direction, one-to-many relationship:

  • From: Product[ProductKey]

  • To: Sales[ProductKey]

Before filtering, the category breakdown and grand total are:

Category Sales Amount
Accessories 450
Electronics 500
Total 950

When Product[Category] is filtered to "Accessories", the filtered result is:

Category Sales Amount
Accessories 450

The filter starts in the filter context of the product dimension and propagates through the relationship to Sales.

Best Practices and Common Pitfalls

  • Prefer a star schema: Keep descriptive attributes in dimension tables and event values in fact tables.

  • Use one-to-many relationships: This is usually the clearest and most efficient design for reporting models.

  • Avoid unnecessary bidirectional filtering: It can introduce ambiguous filter paths and increase evaluation cost.

  • Keep relationships active for normal reporting: An inactive relationship does not automatically receive slicer filters. It can be used in a measure with USERELATIONSHIP when an alternate calculation is required.

  • Validate key uniqueness: A dimension key should uniquely identify each dimension row. Duplicate keys can produce incorrect cardinality or ambiguous results.

  • Check unmatched records: Referential integrity should only be enabled when fact rows reliably match dimension records.

  • Do not merge tables merely to create relationships: Keep source data separate when a model relationship can provide the required filtering and lookup behavior.

  • Review relationships in Service: Relationships are deployed with the semantic model and remain part of the model used by reports, refreshes, and row-level security.

17

How do one-to-many and many-to-many relationships differ in Power BI?

In Power BI, cardinality describes how rows in two related tables match. A one-to-many relationship allows one row on the “one” side to match multiple rows on the “many” side, while a many-to-many relationship allows rows on both sides to match multiple rows on the other side.

Read full answer

Applies toMicrosoft Power BI Desktop (current release) and Power BI Service

Core Differences

Characteristic One-to-many Many-to-many
Key matching One unique key matches many rows Multiple rows can match on both sides
Typical use Dimension to fact table Transactions involving multiple dimensions
Filter propagation Usually from the one side to the many side May require bi-directional filtering
Preferred model Star schema Bridge table or star schema with a bridge
Main modelling risk Incorrect relationship direction Ambiguous filter paths and slower queries

A one-to-many relationship is the standard in a star schema. For example, one customer can place many orders, but each order belongs to one customer.

A many-to-many relationship exists when one customer can purchase many products and one product can be purchased by many customers. In production models, this is usually implemented through a bridge table rather than a direct many-to-many relationship.

One-to-Many Example

Suppose Customers contains unique customer records:

CustomerID CustomerName
C101 Ada
C102 Ben

Sales contains multiple transactions for each customer:

SalesID CustomerID Amount
S1 C101 120
S2 C101 80
S3 C102 150

In Model view, create a relationship from:

Customers[CustomerID] → Sales[CustomerID]

Configure it as one-to-many, with single-direction filtering from Customers to Sales.

Filtering for C101 produces the expected result of 200.

Many-to-Many Example

Using the same customers and products, consider these valid customer-product combinations:

CustomerProductKey CustomerID ProductID
C101|P10 C101 P10
C101|P11 C101 P11
C102|P10 C102 P10

Sales stores each transaction against one combination:

SalesID CustomerProductKey Amount
S1 C101|P10 120
S2 C101|P11 80
S3 C102|P10 150

This models many-to-many relationships between customers and products through CustomerProductBridge:

  • Customers[CustomerID] → bridge: one-to-many

  • Products[ProductID] → bridge: one-to-many

  • CustomerProductBridge[CustomerProductKey] → Sales[CustomerProductKey]: one-to-many

Filtering for C101 and P11 returns 80. Filtering only for C101 returns 200, while filtering only for P10 returns 270.

Filter Behaviour and DAX

A relationship’s filter direction determines which side filters the other. With standard single-direction filtering, filters propagate from the one side to the many side in a one-to-many relationship.

In the bridge model, a customer filter reaches Sales through the bridge. The bridge does not need bi-directional filtering; single-direction paths usually provide clearer calculations and better performance.

Use Modeling > New measure for report calculations. Calculated columns are appropriate for row-level bridge keys, but measures should use DAX aggregations such as SUM. Generate the matching Sales[CustomerProductKey] in the source or with Power Query so both sides use identical keys.

Best Practices

  • Prefer a star schema: Keep dimensions at one row per entity and facts at the most useful transaction grain.

  • Use a unique bridge: Store one row per valid relationship, such as each customer-product pair.

  • Avoid unnecessary bi-directional filtering: It can create ambiguous paths and increase query complexity.

  • Document relationship paths: Confirm which dimensions filter each fact, especially when dashboards contain multiple fact tables.

  • Test totals under filters: Verify that filters from both dimensions reach the intended measure without double-counting.

  • Keep visuals focused: Use customer and product slicers or a matrix to make the many-to-many relationships understandable to report users.

18

What is cross-filter direction in Power BI?

Cross-filter direction controls how filters propagate through a relationship in a Power BI data model. In a typical one-to-many star schema, single-direction filtering propagates filters from the dimension table to the fact table, while bidirectional filtering also allows filters to propagate from the fact table back to the dimension table.

Read full answer

Applies toPower BI Desktop (current release) and Power BI Service (current release)

What Cross-Filter Direction Means

Every Power BI relationship has a cardinality and a cross-filter direction. For a one-to-many relationship:

  • Single direction: Filters flow from the “one” side to the “many” side.

  • Both directions: Filters can flow in either direction.

For example, a relationship between Products and Sales normally follows this filter path:

Products (one) → Sales (many)

When a report filters Products[Category] = "Accessories", the filter propagates to Sales, allowing a measure such as SUM(Sales[Revenue]) to calculate correctly.

With a bidirectional relationship, a filter applied to Sales can also propagate to Products. This is useful when only fact rows with qualifying activity should remain visible in a dimension list.

How Filter Context Works

A measure is evaluated in a filter context, which can come from:

  • Visual-axis values

  • Slicers

  • Page and report filters

  • Relationships between tables

A single-direction relationship propagates filters in its configured direction. A bidirectional relationship extends propagation in both directions. The relationship must be active for its filters to affect query results.

This is different from row context:

  • A measure starts in filter context and can use CALCULATE to modify it.

  • A calculated column is evaluated row by row in row context.

  • Calculated columns do not automatically propagate filters through relationships in the same way as measures.

For a temporary, measure-specific change, a DAX expression can use CROSSFILTER; this changes the filter behavior for that calculation without changing the model relationship itself.

When Bidirectional Filtering Can Be Useful

Bidirectional filtering is appropriate when a fact table needs to filter a dimension table, for example:

  • Showing only products that have qualifying sales.

  • Filtering customer or product lists based on transaction activity.

  • Supporting dimension membership that depends on a selected customer segment or date.

Bidirectional filtering does not itself create a many-to-many relationship. For complex many-to-many scenarios, use a bridge table or a properly designed many-to-many relationship, depending on the model requirements.

Avoid bidirectional relationships by default. They can introduce additional filter paths, affect measure results, and increase the number of filters that Power BI must evaluate.

Business Example

Assume these tables exist:

Products

ProductKey ProductName
P1 Widget
P2 Gadget
P3 Service

Sales

OrderID ProductKey CustomerKey Revenue
101 P1 C1 100
102 P1 C2 50
103 P2 C1 80
104 P2 C2 120

Customers

CustomerKey Segment
C1 Enterprise
C2 SMB

Create these relationships:

  • Products[ProductKey] → Sales[ProductKey]

  • Customers[CustomerKey] → Sales[CustomerKey]

With single-direction filtering, selecting Customers[Segment] = "Enterprise" filters Sales but does not filter Products. A product visual can therefore still show Service, with a blank revenue value.

If the Products–Sales relationship is changed to Both, the filtered Sales rows propagate back to Products. The expected visual result is:

Product Enterprise Revenue
Widget 100
Gadget 80

Service is removed because it has no matching sales row for the selected segment, and the total enterprise revenue is 180.

Best Practices and Common Pitfalls

  • Use a star schema first: Keep dimensions on the one side and facts on the many side, with single-direction relationships by default.

  • Check for ambiguity: Multiple active filter paths between the same tables can produce ambiguous measures or visual errors.

  • Watch performance: Bidirectional propagation can increase evaluation work, particularly in large models.

  • Validate expected results: Test both dimension rows with facts and dimension rows without facts.

  • Prefer explicit modelling: Use a bridge table or another deliberate design for complex many-to-many requirements rather than adding bidirectional filters as a shortcut.

19

How do active and inactive relationships work with USERELATIONSHIP in Power BI?

An active relationship propagates filters according to its cardinality and cross-filter direction, while an inactive relationship does not propagate filters. USERELATIONSHIP temporarily enables a specific existing relationship within CALCULATE or CALCULATETABLE; commonly, it activates an inactive relationship without changing the model globally.

Read full answer

Applies toPower BI Desktop and Power BI Service (current release)

Core Behavior

Relationship type Filter behavior Typical use
Active Filters propagate according to cardinality and cross-filter direction: one-to-many flows from one to many, while many-to-one flows from many to one The main relationship between a date dimension and a fact table
Inactive Does not automatically propagate filters Alternative business dates, such as order date versus ship date

An active relationship does not have to be bidirectional. In a typical star schema, the single-direction relationship from Date to Sales is active, so selecting a date filters the sales rows.

An inactive relationship is useful when the same fact table contains multiple dates. For example, Sales can relate to Date through both Sales[OrderDate] and Sales[ShipDate]. One relationship remains active, while USERELATIONSHIP temporarily enables the other for a specific measure.

Model Setup

  1. Create a date table covering all relevant dates, with one row per calendar date and a unique Date[Date] column.

  2. In Model view, create a one-to-many, single-direction relationship from Date[Date] to Sales[OrderDate]. Keep this relationship active.

  3. Create another one-to-many relationship from Date[Date] to Sales[ShipDate].

  4. Select the second relationship and use Edit relationship to set Cross-filter direction to None, making it inactive. Ensure the OrderDate relationship remains the active relationship.

  5. Use Table tools → Mark as date table for the date dimension.

USERELATIONSHIP requires an existing relationship whose columns are supplied to the function; it cannot create a relationship.

Expected Result

Sample sales data:

SalesID OrderDate ShipDate Amount
1001 2024-01-05 2024-01-07 100
1002 2024-01-20 2024-01-10 200

With Date[Date] in a table visual:

Date Sales Amount Shipped Amount
2024-01-05 100 Blank
2024-01-07 Blank 100
2024-01-10 Blank 200
2024-01-20 200 Blank

The first measure follows the active order-date relationship. The second uses the inactive ship-date relationship through USERELATIONSHIP. A blank means that no sales rows matched the date under that measure.

Code Example

Create the base measure:

// Base measure; its date behavior comes from the active OrderDate relationship.
Sales Amount =
VAR TotalSales =
    SUM ( 'Sales'[Amount] )
RETURN
    TotalSales

Create a measure that uses the inactive ship-date relationship:

// Enable the existing inactive ShipDate relationship only for this calculation.
Shipped Amount =
VAR ShipDateRelationship =
    USERELATIONSHIP ( 'Sales'[ShipDate], 'Date'[Date] )
RETURN
    CALCULATE (
        [Sales Amount],
        ShipDateRelationship
    )

USERELATIONSHIP receives the columns from the existing relationship. Here, Sales[ShipDate] is the many-side column and Date[Date] is the one-side column.

Best Practices and Pitfalls

  • Use measures: Calculated columns are evaluated during model processing or refresh and do not dynamically respond to visual filters or relationship switching.

  • Respect supported relationships: USERELATIONSHIP requires an existing single-direction relationship with one-to-many or many-to-one cardinality. It cannot be used with a bidirectional or one-to-one relationship.

  • Keep the star schema simple: Prefer one active relationship between each dimension and fact table. Add inactive relationships only for genuinely different business roles or dates.

  • Avoid unnecessary bidirectional filtering: It can introduce ambiguity and performance problems. An explicitly selected relationship is often clearer.

  • Do not confuse direction and cardinality: Cardinality identifies the one and many sides; cross-filter direction controls how filters travel.

  • Name measures by business meaning: Labels such as Sales Amount by Ship Date communicate which relationship the measure uses.

  • Review filter context: USERELATIONSHIP changes the relationship used by that calculation, while other relevant filters, such as product or customer filters, still apply.

20

What is a date table in Power BI, and why do you need one?

A date table is a model table containing one row for every date in a continuous date range, with calendar attributes such as year, month, and weekday stored in columns. It provides a single, validated chronology for filtering facts and performing consistent period comparisons.

Read full answer

Applies toMicrosoft Power BI Desktop (current release) and Power BI Service models published from it

What a Date Table Contains

  • Date key: Unique, nonblank dates with no gaps. It must cover every relevant date in the fact tables.

  • Calendar attributes: Typical columns include Date, Year, Month Number, Month Name, Quarter, Day of Week, and Weekday Number.

  • Business attributes: Fiscal year, fiscal period, holiday flags, working-day indicators, and week-ending dates can support operational reporting.

  • Marking: In Power BI Desktop, select the table and choose Table tools → Mark as date table, then select the date column. The command validates that the column contains unique, contiguous dates and identifies the table as a date table.

Marking does not create relationships, hierarchy columns, or custom sort orders. It also does not automatically make month names sort chronologically.

Why You Need One

  • Reliable time intelligence: Functions such as DATEADD, SAMEPERIODLASTYEAR, TOTALYTD, and DATESBETWEEN work predictably when they have a valid date table, a date column, and correctly configured relationships. Marking the table alone is not sufficient.

  • Consistent reporting: All date-based facts can share the same calendar definitions, preventing differences between measures and visual filters.

  • Correct sorting and hierarchies: Analysts can sort Month Name by Month Number and build an explicit Date → Year → Quarter → Month hierarchy rather than depending on implicit auto date/time behavior.

  • Role-playing dates: OrderDate, ShipDate, and InvoiceDate can use one date dimension through separate relationships, avoiding duplicated calendar tables and ambiguous active relationships.

  • Period comparisons: A sufficiently broad date range supports prior-period, prior-year, year-to-date, and rolling-period calculations.

Modeling Requirements

  • Relate each fact table to the date table with a many-to-one relationship: many fact rows correspond to one date-table row.

  • Use single-direction filtering from the date table to facts for standard calendar analysis.

  • For multiple date roles, keep the principal relationship active and additional relationships inactive; measures can activate a role temporarily with USERELATIONSHIP.

  • Include all fact dates plus additional history needed for comparisons.

  • Built-in DAX time-intelligence functions use calendar semantics. Custom fiscal-year and fiscal-period reporting generally requires additional fiscal attributes and tailored DAX measures.

Business Example

An actual date table must contain every date in the displayed range; the excerpt is abbreviated.

Date Year Month Name Quarter
2023-01-01 2023 January Q1
2023-01-02 2023 January Q1
2024-01-01 2024 January Q1
2024-01-02 2024 January Q1

Sales facts:

OrderDate Amount
2023-01-05 200
2024-01-05 250

For January 2024, a same-period-prior-year measure should evaluate current sales independently from prior-year sales:

  • Current sales: 250

  • January 2023 sales: 200

  • Variance: 50

  • Percentage increase: 25%

Best Practices and Common Pitfalls

  • Create an explicit date hierarchy and configure Sort by column for month and weekday names.

  • Use one date table for the standard calendar; add another only when a genuinely different calendar is required.

  • Keep the table as narrow as practical because unnecessary columns increase model size and refresh work.

  • Do not rely on the date table to fix invalid relationships or incorrect measures; test time intelligence with representative filters.

  • Include enough history for the reporting comparisons used by the business, rather than only the current year.

21

What does DAX mean in Power BI?

DAX (Data Analysis Expressions) is the formula language used in Power BI to create measures, calculated columns, and calculated tables. It supports aggregation, time intelligence, and business logic across a Power BI data model.

Read full answer

Applies toMicrosoft Power BI Desktop and Power BI Service (current release)

How DAX Works

DAX expressions operate on tables, columns, and values. Unlike a procedural language, a DAX formula describes the result to calculate rather than a sequence of commands.

The three main calculation types are:

Calculation Purpose Evaluated
Measure Calculates an aggregate or KPI for the current query and filter context Query time
Calculated column Adds a value to every row of an existing table Data refresh
Calculated table Creates a new table using DAX Data refresh

For example, a measure calculating revenue uses SUM, while a calculated column calculating order value multiplies row-level fields such as Quantity and Unit Price.

Measures, Columns, and Tables

A measure is usually the correct choice for totals, percentages, comparisons, and other aggregations. It responds dynamically to slicers, filters, axes, and interactions in a report.

A calculated column evaluates one row at a time and stores its result in the model. A calculated column for line revenue, for example, would multiply each row’s Quantity by its Unit Price. It is suitable for row-level information that should remain visible in a table or be used in a visual.

A calculated table materializes new rows and columns. It can summarize data, create date tables, or support advanced model transformations. Because it is stored in the model, unnecessary calculated tables can increase memory usage and refresh time.

Filter Context and CALCULATE

DAX distinguishes two important concepts:

  • Row context: The current row being evaluated, primarily used in calculated columns and iterator functions such as SUMX.

  • Filter context: The rows permitted by current filters, relationships, slicers, and visual fields.

CALCULATE is the primary function for modifying filter context. It evaluates an expression after its filter arguments have added, replaced, or removed filters.

A measure can use VAR to store the current revenue and RETURN to return it. Variables improve readability and make intermediate results reusable, but they do not create a new filter context.

DAX Example

Assume a Sales table contains:

Date Region Product Amount
2025-01-15 North Widget 100
2025-02-10 North Widget 150
2026-01-12 North Widget 180
2026-02-14 North Widget 220

A contiguous Date table can cover 1 January 2025 through 31 December 2026 by using CALENDAR with the DATE function. Relate Date[Date] to Sales[Date] using a one-to-many, single-direction relationship, then mark Date as the date table.

The Revenue measure sums Sales[Amount]. The Prior Year Revenue measure uses CALCULATE to evaluate the same sum under SAMEPERIODLASTYEAR for Date[Date]. The Revenue Growth % measure uses DIVIDE to calculate the difference between current and prior-year revenue as a percentage of prior-year revenue.

For a visual showing months of 2026, the expected results are:

Month Revenue Prior Year Revenue Revenue Growth %
January 2026 180 100 80.00%
February 2026 220 150 46.67%

SAMEPERIODLASTYEAR requires an appropriate date table and relationship. DIVIDE handles a zero or blank denominator without returning a division error.

Best Practices

  • Prefer measures for KPIs: Use calculated columns only when stored row-level values are necessary.

  • Model with a star schema: Keep facts and dimensions separate, and use a marked date table for time intelligence.

  • Control relationship direction: Use single-direction relationships unless bidirectional filtering is genuinely required; excessive bidirectional relationships can introduce ambiguity.

  • Optimize expensive calculations: Reduce repeated table scans, avoid unnecessary iterators, and use Performance Analyzer to identify slow measures.

  • Use variables carefully: VAR improves readability and can prevent repeated evaluation of complex expressions.

  • Design clear visuals: Apply formats explicitly, use meaningful field names, and limit excessive visual elements so users can interpret results quickly.

  • Test security and refresh behavior: When implementing row-level security, base access rules on stable identity columns and verify performance with representative data volumes.

22

What is the difference between a calculated column and a measure in Power BI?

A calculated column is a DAX expression evaluated for every row and stored in the model, while a measure is evaluated dynamically for each visual or query according to its filter context. Calculated columns are appropriate for row-level calculated fields; measures are preferred for aggregations, KPIs, and context-sensitive business calculations.

Read full answer

Applies toMicrosoft Power BI Desktop (current release) and Power BI Service (current release)

Core Differences

Aspect Calculated column Measure
Purpose Creates a calculated field for each row of an existing table Defines a calculation evaluated according to the current query
Evaluation context Primarily row context Primarily filter context
Evaluation time Calculated during refresh and stored in an Import model; evaluated at query time in a DirectQuery model Evaluated when a visual, report, or user query requests its value
Typical use Margin per transaction, row-level categories, flags, or age groups Revenue, totals, averages, ratios, percentages, and time comparisons
Interaction with filters A stored row value is filtered or grouped like any other column Recalculates as slicers, page filters, visual interactions, and relationships change the filter context
Model impact Can increase Import model size and refresh workload Stores only the definition, although evaluation can be computationally expensive

Row Context and Filter Context

A calculated column is evaluated one row at a time. Within that row, column references such as Amount and TaxRate identify the values belonging to the current row. A calculated column can also explicitly modify filter context with CALCULATE, but its defining characteristic is row-level evaluation.

A measure is evaluated in the filter context established by the visual or query. Date, product, region, and other filters can flow through model relationships and change the measure’s result.

When a measure needs to perform a row-by-row calculation, an iterator such as SUMX introduces row context by iterating over a table. CALCULATE, by contrast, modifies the existing filter context; it does not itself introduce row context.

Business Example

Consider an Orders table:

OrderID OrderDate Amount TaxRate
101 2025-01-10 100 20%
102 2025-01-11 200 10%

Suppose AmountWithTax is a calculated column that adds the row’s tax to its amount. The result is:

OrderDate AmountWithTax
2025-01-10 120
2025-01-11 220

A measure that sums AmountWithTax returns 340 for all rows and 120 when the visual is filtered to 2025-01-10. The same result can be produced dynamically with a measure that iterates the Orders table and calculates the tax-inclusive amount for each row, without storing an AmountWithTax column.

Performance and Modeling

  • Prefer measures for aggregations, ratios, KPIs, and calculations that should respond dynamically to filters.

  • Use a calculated column when every row needs a stored value that can be grouped, sliced, sorted, or referenced by other calculations.

  • In Import models, calculated columns increase storage requirements and refresh work. In DirectQuery models, they are normally evaluated during queries, so their query-time cost must be tested.

  • Build a star schema with fact and dimension tables. Use one-to-many relationships from dimension tables to fact tables, with the fact table on the many side and the intended filter direction.

  • Place measures in a dedicated measures table when the model contains several fact tables or many measures. This simplifies organization and can make relationship behavior easier to control.

  • Keep calculated columns at the correct grain. For example, calculate transaction margin on an order-line table rather than once per customer in a customer table.

  • Do not create calculated columns merely to store an intermediate value when a measure can perform the same calculation efficiently.

Visual Design

  • Use calculated columns in detail tables and matrices when every underlying row needs its own calculated value, such as margin, risk category, or transaction status.

  • Use measures in cards, charts, matrices, and KPI visuals for totals, averages, ratios, and context-sensitive values.

  • Make the visual grain explicit: a calculated column contains a value for every underlying row, while a measure returns a value for each visible grouping or total.

  • Prefer dimension columns for slicers, especially when the calculated column has high cardinality, because a calculated column can still create a large filter set.

  • Format calculated columns and measures consistently for currency, percentages, and dates so users can interpret both types of values correctly.

23

How do row context and filter context work in Power BI DAX?

Row context identifies the values of the current row, while filter context determines which rows are visible to a DAX expression. Understanding their interaction is essential for writing correct measures, calculated columns and iterator expressions in Power BI.

Read full answer

Applies toPower BI Desktop (current release) and Power BI Service

How Contexts Work

DAX evaluates expressions using two related but distinct contexts:

  • Row context: The values from one specific row. For example, in a calculated column, Sales[Amount] refers to the amount in the row currently being evaluated.

  • Filter context: The set of filters affecting an expression. These filters can come from visual filters, slicers, relationships, row-level security or CALCULATE.

A common misconception is that row context automatically filters the table. It does not. Row context merely exposes the current row’s values; filter context determines which rows other expressions can aggregate.

Row Context

Row context is introduced automatically in:

  • Calculated columns

  • Calculated tables during row-by-row evaluation

  • Iterator functions such as SUMX, AVERAGEX and CONCATENATEX

Within an iterator, each row becomes the current row. This allows expressions such as Sales[Amount] to retrieve that row’s amount before the result is aggregated.

For the following sales data, a calculated column can calculate a row-level margin:

Date ProductKey Region Amount Cost
2025-01-01 1 West 100 60
2025-01-02 2 East 50 30
2025-01-03 1 West 150 90
2025-01-04 2 East 100 70

The calculated column Margin Amount evaluates independently for each row and produces margins of 40, 20, 60 and 30.

Filter Context

Filter context comes from several sources:

  • Filters selected in a slicer or the Filters pane

  • Filters applied to a visual or report page

  • Relationships in the data model

  • Row-level security rules

  • Filter arguments supplied to CALCULATE

For example, if the model has a one-to-many relationship from Product to Sales, selecting Widget in a slicer filters the Product table and propagates that filter to related Sales rows.

CALCULATE adds or modifies the existing filter context. A Boolean filter on a column normally replaces existing filters for that column unless KEEPFILTERS is used.

Context Transition

Context transition occurs when a measure is evaluated inside a row context. For example, SUMX iterates over Product, while CALCULATE evaluates [Total Sales] for each product.

The outer iterator provides row context. CALCULATE transitions that row context into a temporary filter context, allowing the measure to calculate the sales total for the current product.

This differs from simply referencing Sales[Amount] in an iterator: context transition makes the complete measure evaluate under the current row’s filters.

Code Example

Create the tables above in Power BI Desktop, then select Modeling > New column and add:

Margin Amount =
VAR SalesAmount = Sales[Amount]
VAR SalesCost = Sales[Cost]
RETURN
    SalesAmount - SalesCost

Select Modeling > New measure and create:

Total Sales =
VAR TotalAmount = SUM(Sales[Amount])
RETURN
    TotalAmount

Create another measure to demonstrate context transition:

Sales by Product =
SUMX(
    Product,
    VAR CurrentProductName = Product[Product Name]
    VAR AmountForProduct =
        CALCULATE(
            [Total Sales],
            Product[Product Name] = CurrentProductName
        )
    RETURN
        AmountForProduct
)

A filter-aware measure can also use CALCULATE directly:

West Sales =
VAR WestAmount =
    CALCULATE(
        [Total Sales],
        Sales[Region] = "West"
    )
RETURN
    WestAmount

Expected results with no visual filters are:

Result Expected Value
[Total Sales] 400
[West Sales] 250
[Sales by Product] for Widget 250
[Sales by Product] for Gadget 150
[Sales by Product] total 400

Best Practices

  • Use a star schema, with dimensions related one-to-many to the fact table.

  • Use single-direction relationships from dimensions to facts unless a specific bidirectional requirement exists.

  • Prefer measures for aggregations because they respond dynamically to filter context.

  • Use calculated columns only when a row-level result is required; they increase model size and refresh time.

  • Use VAR and RETURN to make context behavior easier to trace and expressions easier to debug.

  • Avoid unnecessary context transitions in large iterator expressions because repeatedly transitioning context can affect performance.

  • Keep filters visible through slicers and clear visual titles so users understand which filter context produced each result.

24

How does CALCULATE modify filter context in Power BI DAX?

CALCULATE evaluates a DAX expression in a modified filter context. Its filter arguments can add a filter, overwrite filters on referenced columns or tables, or remove existing filters; unaffected filters remain in force.

Read full answer

Applies toPower BI Desktop and Power BI Service (current release)

How CALCULATE Changes Context

A measure normally receives a filter context from slicers, visual-level filters, row-level security, and relationship propagation. CALCULATE temporarily modifies that context while evaluating its expression.

Its arguments have two roles:

  • Expression: The calculation to evaluate, such as a measure or an aggregation such as SUM(Sales[Amount]).

  • Filter arguments: Boolean or table expressions that determine how the filter context is changed.

For example, CALCULATE([Total Sales], Products[ProductName] = "Widget A") evaluates [Total Sales] with a Widget A filter. CALCULATE does not automatically discard existing filters; it only removes or replaces them when instructed to do so.

Filter Modifiers

Modifier Effect
Boolean filter Adds a filter and overwrites existing filters on the referenced columns
KEEPFILTERS Intersects the supplied filter with the existing filter context
REMOVEFILTERS Removes filters from the specified table or columns
ALL Removes filters from an entire table or specified columns
ALLSELECTED Removes filters applied inside the measure while retaining filters in the query context

The distinction between removing a filter and excluding a selected value is important. Removing the product filter restores all products; excluding the selected product requires an explicit condition that rejects that value.

Worked Business Example

Assume the following star-schema data:

ProductKey ProductName
1 Widget A
2 Widget B
Date ProductKey Amount
2025-01-10 1 100
2025-01-12 2 200
2025-02-08 1 150
2025-02-10 2 250

The relationship is Products[ProductKey] to Sales[ProductKey], and [Total Sales] is defined as SUM(Sales[Amount]).

With Widget A selected and no other filters, the expected results are:

Evaluation Result
[Total Sales] in the current report context 250
CALCULATE with an explicit Widget A filter 250
CALCULATE with REMOVEFILTERS(Products[ProductName]) 700
CALCULATE with an explicit condition excluding Widget A 450

The third evaluation is an all-products total, not an exclusion total. The fourth returns 450 because it keeps only Widget B.

Row Context and Context Transition

Measures use filter context, while calculated columns and calculated tables have a row context. Calling CALCULATE from a row context can trigger context transition: the current row’s values become filters for related tables while the measure expression is evaluated.

This behavior is useful for row-by-row related calculations but can unintentionally change aggregation results. Use RELATED or RELATEDTABLE when a direct relationship lookup is sufficient.

Best Practices and Common Pitfalls

  • Prefer explicit REMOVEFILTERS over broad ALL calls when only particular columns should lose their filters.

  • Use a star schema with clearly defined relationships and appropriate filter direction.

  • Avoid unnecessary context transitions and test measures with filters, grand totals, blank selections, and multiple selections.

  • Use variables to make complex filter logic readable and avoid repeated evaluation.

  • Document whether a calculated field is a measure, calculated column, or calculated table column so consumers understand how it responds to filters.

25

What is the difference between SUM and SUMX in Power BI DAX?

SUM adds the values in a numeric column, while SUMX evaluates an expression for each row of a table and then adds the results. SUMX is useful when each row must first be calculated, such as multiplying quantity by unit price.

Read full answer

Applies toPower BI Desktop (current release)

How They Work

  • SUM: Aggregates the values directly in one numeric column.

  • Example: SUM('Sales'[Amount])

  • Blank values are ignored.

  • If the filtered table contains no rows or only blanks, it returns BLANK().

  • SUMX: Iterates over every row in a table and evaluates a DAX expression for each row before summing the results.

  • Example: SUMX('Sales', 'Sales'[Amount] * 'Sales'[Quantity])

  • The expression can reference the current row through columns of the iterated table.

  • Blank expression results are ignored during aggregation.

  • If the iterated table has no rows, SUMX returns BLANK().

Filter Context and Row Context

Both functions respect the current filter context. For example, if a report filters Category to "A", only the visible category’s rows are aggregated.

SUMX additionally introduces a row context while evaluating its expression. In the expression 'Sales'[Amount] * 'Sales'[Quantity], each row sees its own values, and SUMX then adds the calculated values together. A row context does not automatically filter a related table.

If a related table needs to be used inside the row calculation, CALCULATE can establish the relevant filter context. For example, with a relationship from Sales to Products, SUMX('Sales', 'Sales'[Quantity] * CALCULATE(MAX('Products'[Price]))) evaluates the related product price for each sales row before calculating the total.

Business Example

Assume this sample table exists:

Product Category Amount Quantity
A Hardware 100 2
B Hardware 50 1
C Software 200 4
D Software 80 2

The expected results are:

Calculation Result
SUM('Sales'[Amount]) 430
SUMX('Sales', 'Sales'[Amount] * 'Sales'[Quantity]) 1,210

The second result is calculated as:

(100 × 2) + (50 × 1) + (200 × 4) + (80 × 2) = 1,210

Best Practices

  • Use SUM for a straightforward total of a numeric column.

  • Use SUMX when the aggregation requires a row-by-row calculation.

  • Keep the table iterated by SUMX as small as practical. Iterating a large fact table can be expensive, particularly with complex expressions.

  • Use a properly modeled star schema, with dimensions related to the fact table, so filter context is applied efficiently and predictably.

  • Prefer explicit measures over calculated columns for values that depend on report filters or user interaction.

  • Test totals with slicers, date filters, and row-level security because both functions respond to the active filter context.

26

How do iterator functions work in Power BI DAX?

Iterator functions in DAX evaluate an expression for every row of a table and then aggregate or combine the results. They are useful when each row must contribute a calculated value, such as Quantity * UnitPrice, rather than simply aggregating an existing column.

Read full answer

Applies toMicrosoft Power BI Desktop and Power BI Service (current release)

How They Work

Most aggregation iterator functions take two arguments:

  1. A table expression containing the rows to iterate over.

  2. An expression evaluated for each row.

For example, SUMX accepts a table and a row-level expression. During iteration, the expression has access to the current row through a row context. This differs from filter context, which determines the rows visible to a table expression or measure.

A measure referenced inside an iterator is evaluated under the iterator’s outer filter context. The current row does not automatically become a filter; use CALCULATE when the row context should be converted into a filter context for that row.

Common Iterator Functions

Function Purpose Example result
SUMX Sums an expression across rows Total calculated revenue
COUNTX Counts rows where the expression is nonblank Number of rows with sales
AVERAGEX Calculates the average of row-level values Average transaction value
MINX Finds the minimum row-level value Lowest calculated margin
MAXX Finds the maximum row-level value Highest calculated revenue
PRODUCTX Multiplies row-level values Compounded growth or rate
CONCATENATEX Concatenates row-level values Comma-separated product list

Use SUM instead of SUMX when the required result is simply the aggregation of an existing column. Iterator functions are most appropriate when the calculation depends on multiple columns or requires row-by-row logic.

Worked Business Example

Suppose a star-schema model contains a Product dimension and a Sales fact table.

Product dimension:

Product ID Product Name Standard Price
A Widget A 100
B Widget B 150

Sales fact table:

Order Date Product ID Units Unit Price
2025-01-03 A 10 100
2025-01-05 B 5 150
2025-02-02 A 4 100

A measure calculates revenue without requiring a calculated Revenue column:

Revenue =
VAR SalesRows = 'Sales'
RETURN
    SUMX ( SalesRows, SalesRows[Units] * SalesRows[UnitPrice] )

With a one-to-many, single-direction relationship from Product to Sales, placing Product Name on rows and Revenue on values produces:

Product Name Revenue
Widget A 1,400
Widget B 750
Total 2,150

For Widget A, the calculation is (10 * 100) + (4 * 100) = 1,400. The grand total is 1,400 + 750 = 2,150. This differs from SUM('Sales'[Units]), which directly sums an existing numeric column.

Code Example

A weighted average can be calculated by dividing total value by total units. RELATED retrieves the standard price because Product is on the one side of the active one-to-many relationship:

Weighted Average List Price =
VAR SalesRows = 'Sales'
VAR TotalStandardValue =
    SUMX (
        SalesRows,
        SalesRows[Units] * RELATED ( 'Product'[StandardPrice] )
    )
VAR TotalUnits =
    SUMX ( SalesRows, SalesRows[Units] )
RETURN
    DIVIDE ( TotalStandardValue, TotalUnits )

For the sample data, the expected result is (2,150 ÷ 19) = 113.16. If the total number of units is zero, DIVIDE returns BLANK.

Best Practices and Pitfalls

  • Prefer a star schema with a fact table and dimension tables. Avoid unnecessary many-to-many relationships and bidirectional filtering.

  • Use SUM for simple column aggregations because it is generally simpler and more efficient than SUMX.

  • Use a measure rather than a calculated column when the calculation should respond dynamically to slicers, dates, or report filters.

  • Avoid deeply nested iterator expressions and repeated expensive calculations. Use VAR and RETURN for readability and to make evaluation logic explicit.

  • Keep iterator expressions simple and avoid placing expensive transformations inside the iterated expression.

  • Check results against a known control total, especially when relationships, blank values, or filtered tables are involved.

27

How do ALL, ALLEXCEPT, and ALLSELECTED differ in DAX?

In DAX, ALL, ALLEXCEPT, and ALLSELECTED modify a measure’s filter context. ALL removes filters from its specified target, ALLEXCEPT retains specified columns while clearing other filters on a table, and ALLSELECTED removes query-level filters on its target while preserving context established outside the query.

Read full answer

Applies toPower BI Desktop (current release) and Power BI Service (current release)

Core Differences

Expression Behaviour
ALL(Sales) Removes filters applied to columns of Sales; filters on other tables remain and may affect Sales through relationships.
ALL(Sales[Product]) Removes only the filter on Sales[Product], retaining filters on other columns and tables.
ALLEXCEPT(Sales, Sales[Year]) Removes filters on Sales except Sales[Year]. It preserves the year filter and removes the product breakdown.
ALLEXCEPT(Sales, Sales[Product]) Removes filters on Sales except Sales[Product]. It preserves the product filter and removes the year filter.
ALLSELECTED(Sales) Removes filters on Sales introduced within the current query while retaining filters from outside that query.
ALLSELECTED() Removes all filters introduced within the current query while retaining filters from outside it.
ALL() Used as a CALCULATE filter modifier, removes filters from all tables; used as a table expression, it returns all rows while ignoring filters.

A common source of confusion is ALLEXCEPT: the columns listed after the table are the exceptions that are retained, not the filters that are removed.

Filter Context and Data Modelling

These functions are normally used as filter modifiers inside CALCULATE. They do not create row context, and they do not bypass row-level security; security filters remain in effect.

In a star schema, ALL(Sales) does not remove a filter from a related Product dimension. That dimension filter can still restrict Sales through the relationship. Use ALL(Product) when the intention is to remove the product-dimension filter, or ALL(Date) to remove date-dimension filters.

ALLSELECTED is query-relative. Filters from slicers, report filters, or page filters are generally outside the visual’s row-and-column query and are retained. Filters introduced within the query are removed to the extent covered by the function’s table or column arguments. Use ALLSELECTED() rather than ALLSELECTED(Sales) when the total should ignore all query-level filters in the visual.

Worked Example

Assume Sales contains:

Year Product Amount
2024 A 100
2024 B 80
2025 A 120
2025 B 60

Assume [Sales Amount] sums Sales[Amount], and the current visual applies the direct filters Sales[Year] = 2024 and Sales[Product] = A.

Calculation Result Reason
[Sales Amount] 100 Uses both filters
CALCULATE([Sales Amount], ALL(Sales)) 360 Removes both filters on Sales
CALCULATE([Sales Amount], ALLEXCEPT(Sales, Sales[Year])) 180 Retains 2024 and removes the product filter
CALCULATE([Sales Amount], ALLEXCEPT(Sales, Sales[Product])) 220 Retains product A and removes the year filter
CALCULATE([Sales Amount], ALLSELECTED(Sales)) 360 Removes both filters because they were introduced inside the query

The origin of a filter changes the ALLSELECTED result:

Filter origin Result
Year inside the query, product outside it 220
Year outside the query, product inside it 180
Both filters outside the query 100
Both filters inside the query 360

Practical Guidance

  • Use ALL against the exact table or columns whose filters should be removed; it does not globally clear related dimension filters.

  • Use ALLEXCEPT when one or more columns must remain filtered and the other filters on that table should be cleared.

  • Use ALLSELECTED() for a visual-level total that responds to external slicers but ignores row, column, and visual-level query filters.

  • Prefer REMOVEFILTERS when removing filters is the primary objective; it expresses intent more clearly than ALL while providing equivalent filter-modifier behaviour.

  • Test totals in the actual report because filters can originate from slicers, visuals, report or page filters, relationships, and row-level security.

28

What does the DAX REMOVEFILTERS function do?

REMOVEFILTERS is a DAX table function that removes filters from a specified table or columns, commonly as a filter argument to CALCULATE in a measure. It affects the calculation’s filter context without changing the state of slicers or other report visuals.

Read full answer

Applies toMicrosoft Power BI Desktop (current release)

How It Works

REMOVEFILTERS ignores specified filters while evaluating the surrounding calculation:

  • REMOVEFILTERS(Products) removes filters from all columns in Products.

  • REMOVEFILTERS(Products[Category]) removes only the filter on Products[Category].

  • Filters on unrelated tables normally remain active, although relationship and expanded-table behavior must be considered.

  • It does not change row context; it modifies the filter context used by the calculation.

  • It does not clear a slicer or change the visual’s displayed state.

For filter-removal purposes, it is similar to ALL, although ALL can also return a table containing all rows. REMOVEFILTERS usually communicates the intended behavior more clearly.

Business Example

Consider these tables in a star schema:

Products

ProductKey Product Category
1 Keyboard Electronics
2 Monitor Electronics
3 Mouse Accessories

Sales

SalesKey ProductKey Amount
1 1 100
2 2 200
3 3 50

The tables have a one-to-many relationship from Sales[ProductKey] to Products[ProductKey].

Suppose [Total Sales] sums Sales[Amount]. A second measure evaluates [Total Sales] with REMOVEFILTERS(Products), while a third evaluates it with REMOVEFILTERS(Products[Category]).

Place Products[Category] and these measures in a table visual, then filter Category to Electronics:

Category Total Sales Total Sales - All Products Total Sales - All Categories
Electronics 300 350 350
  • [Total Sales] respects the Electronics filter and returns 300.

  • [Total Sales - All Products] removes all filters on Products and returns 350.

  • [Total Sales - All Categories] removes the category filter and also returns 350 in this model.

When to Use It

Use REMOVEFILTERS when a measure must calculate a total independently of filters on a particular dimension. Typical examples include:

  • Calculating a percentage against a total independent of the current product filter.

  • Calculating sales excluding the selected category.

  • Comparing sales contribution with an unrestricted product total.

  • Evaluating alternative calculations without specific dimension filters.

For example, ALLEXCEPT(Products, Products[Category]) can preserve the selected category while removing other product-level filters for a category-sales numerator. REMOVEFILTERS(Products) can provide the unrestricted product total for the denominator. For Electronics, the resulting share is 300 / 350, or approximately 85.7%.

Best Practices and Pitfalls

  • Be specific: Prefer REMOVEFILTERS(Products[Category]) when only one column’s filter should be removed.

  • Preserve intended filters: A date filter remains unless it is explicitly removed or otherwise affected by relationship or expanded-table behavior.

  • Use ALLSELECTED carefully: Use it when a calculation needs to retain explicitly selected slicer values while disregarding other query or visual context; it does not have the same purpose as REMOVEFILTERS.

  • Avoid unnecessary filter removal: Repeatedly removing filters can increase model evaluation work. Test measure performance with the Power BI Performance Analyzer.

  • Document the behavior: Name measures clearly, such as “Sales excluding Product filter,” so consumers understand why the result differs from the visible filtered total.

29

What is the difference between VALUES and DISTINCT in Power BI DAX?

In Power BI DAX, VALUES and DISTINCT are filter-aware table functions that remove duplicates. For a column, VALUES(Sales[Customer]) and DISTINCT(Sales[Customer]) are equivalent; the important practical difference is that DISTINCT can also process a broader table expression.

Read full answer

Applies toPower BI Desktop and Power BI Service (current release)

How They Work

  • VALUES(<column reference>): Returns a one-column table containing the distinct values of the referenced column. It is especially useful for retrieving the currently available members of a dimension.

  • VALUES(<table reference>): Returns a table with duplicate rows removed across all columns.

  • DISTINCT(<column reference>): Returns the same one-column result as VALUES for the same column.

  • DISTINCT(<table expression>): Removes duplicate rows from a table expression, including a calculated or otherwise derived table.

Both functions respond to the applicable filter context. They therefore work well in measures and calculated tables that need to reflect report filters.

Sample Example

Consider this Sales table:

Customer Product
Acme Widget
Acme Widget
Beta Gadget
Blank Unknown

The expressions VALUES ( Sales[Customer] ) and DISTINCT ( Sales[Customer] ) both return the distinct customers:

Customer
Acme
Beta
Blank

Likewise, both VALUES ( Sales ) and DISTINCT ( Sales ) remove the duplicate Acme | Widget row and return:

Customer Product
Acme Widget
Beta Gadget
Blank Unknown

The distinction becomes more useful with a derived table. For example, DISTINCT ( SUMMARIZE ( Sales, Sales[Customer], Sales[Product] ) ) can remove duplicate combinations created by a table expression. This makes DISTINCT the more general choice when the input is not simply a referenced column or table.

Filter Context and Blank Values

Filters affect only values reached by the relevant filter propagation. For example, filtering Sales[Year] directly affects VALUES ( Sales[Year] ). In a standard star schema, however, a filter on the Sales fact table does not automatically propagate backward through a one-way relationship to DimCustomer; VALUES ( DimCustomer[CustomerName] ) may therefore still contain every customer in the dimension.

Both VALUES and DISTINCT can include a BLANK row in their returned table. For example, if the source column contains an actual blank, that blank remains one distinct value rather than being removed automatically.

DISTINCTCOUNT has different blank-handling semantics: it counts BLANK as a distinct value. When blanks must be excluded, use DISTINCTCOUNTNOBLANK or filter the source values explicitly before counting them.

Choosing the Right Function

Requirement Recommended function
Distinct values from one column VALUES or DISTINCT
Distinct rows from a directly referenced table VALUES or DISTINCT
Distinct rows from a calculated table expression DISTINCT
Count distinct values, including blank DISTINCTCOUNT
Count distinct values, excluding blank DISTINCTCOUNTNOBLANK

For readability, VALUES is often clearer when the intent is simply “the available values of this column.” DISTINCT communicates uniqueness more explicitly and is particularly useful when applying uniqueness to a combination of columns or to a derived table.

Best Practices

  • Prefer a star schema and retrieve dimension members from dimension tables rather than repeatedly deduplicating large fact tables.

  • Use VALUES for ordinary dimension-column membership and DISTINCT when uniqueness applies to multiple columns or a calculated table.

  • Do not assume that filtering a fact table automatically filters its dimensions; check relationship direction and filter propagation.

  • Test measures with blank rows because VALUES, DISTINCT, and DISTINCTCOUNT do not all handle blanks in the same way.

  • Avoid repeatedly applying DISTINCT to large fact tables in multiple measures because this can increase memory consumption and processing time.

31

How do you calculate year-to-date sales with DAX in Power BI?

Year-to-date (YTD) sales is calculated by applying a DATESYTD date filter to the total sales measure inside CALCULATE. This creates a dynamic measure that responds to report filters, date hierarchies, and slicers.

Read full answer

Applies toPower BI Desktop (current release) and Power BI Service

How It Works

The calculation follows these steps:

  1. [Total Sales] sums the sales amount from the fact table.

  2. DATESYTD('Date'[Date]) returns the dates from January 1 through the end of the relevant period in the current year.

  3. CALCULATE evaluates [Total Sales] over those YTD dates.

  4. The measure remains in the filter context of each visual row.

A dedicated date table should contain one row per day, cover the full reporting period, and be connected to Sales[Order Date] with a one-to-many relationship.

Filter Context and Visual Behavior

  • Month rows: A matrix showing Year and Month displays cumulative sales through the end of each month.

  • Year row: The value represents the full YTD total for that year.

  • Slicers: Filters for customers, products, regions, or years remain active and restrict the YTD calculation.

  • Calculated columns: A YTD calculated column would be evaluated row by row and would not respond correctly to report slicers, so a measure is preferred.

  • Future dates: If the date table contains future dates but there are no future transactions, they do not affect sales. Future-dated transactions are included unless an explicit cutoff is applied.

Business Example

Suppose the Sales table contains:

Order Date Sales Amount
2025-01-10 1,000
2025-02-05 1,500
2025-03-12 2,000
2025-03-20 500

The expected monthly YTD results are:

Month Monthly Sales YTD Sales
January 2025 1,000 1,000
February 2025 1,500 2,500
March 2025 2,500 5,000

For example, the March value includes all sales from January through March, producing 5,000.

Code Example

Use a dedicated date table named Date with a column named Date:

Total Sales :=
VAR SalesAmount =
    SUM ( Sales[Sales Amount] )
RETURN
    SalesAmount

Create the main YTD measure:

Sales YTD :=
VAR YTDDates =
    DATESYTD ( 'Date'[Date] )
RETURN
    CALCULATE (
        [Total Sales],
        YTDDates
    )

If the business definition is “sales from January 1 through today,” even when future-dated transactions exist, use:

Sales YTD Through Today :=
VAR CutoffDate =
    TODAY ()
RETURN
    CALCULATE (
        [Total Sales],
        DATESYTD ( 'Date'[Date] ),
        'Date'[Date] <= CutoffDate
    )

The first measure is normally preferred because it follows the available reporting dates. The second explicitly excludes transactions dated after the current date.

Best Practices and Pitfalls

  • Use a proper date table: Create it in Power Query, mark it with Mark as date, and connect it to the sales table with a single-direction, one-to-many relationship.

  • Prefer measures over calculated columns: Measures respond dynamically to filters and slicers, whereas calculated columns are fixed when the model refreshes.

  • Avoid unnecessary bidirectional relationships: They can introduce ambiguity and increase DAX evaluation complexity.

  • Choose the correct cutoff: Decide whether YTD means the latest reporting date, today, the current month-end, or the latest transaction date.

  • Test report context: Check YTD values with year, month, customer, and region slicers to confirm that the filter context produces the intended results.

  • Design visuals clearly: Label the measure “Sales YTD” or “Cumulative Sales,” and format monetary values consistently to prevent users from confusing monthly totals with cumulative totals.

32

How do you calculate year-over-year growth with DAX in Power BI?

Year-over-year (YoY) growth compares revenue or another measure with the equivalent period in the previous year. In Power BI, this is calculated with measures that shift the current filter context back by one year using SAMEPERIODLASTYEAR or DATEADD.

Read full answer

Applies toMicrosoft Power BI Desktop (current release) and Power BI Service

Core Approach

Create three measures:

  • Current revenue: Sum of revenue for the selected period.

  • Prior-year revenue: Current revenue evaluated after shifting the date filter one year backward.

  • YoY growth: Difference or percentage change between current and prior revenue.

CALCULATE applies the shifted date table to the current filter context. Because these calculations are measures, they respond dynamically to slicers, report pages, matrix rows, and drill-down operations.

Required Data Model

Use a dedicated, contiguous Calendar table covering the current reporting period, the previous year, and any forecast period. For example, select Modeling > New table and create:

Calendar =
CALENDAR (
    DATE ( 2023, 1, 1 ),
    DATE ( 2026, 12, 31 )
)

Create a year column with Modeling > New column:

Year = YEAR ( 'Calendar'[Date] )

Create an active, one-to-many relationship from Calendar[Date] to Sales[Date]. Use Calendar[Date] to drive all date filters and time-intelligence calculations.

Code Example

Create the following measures:

Total Revenue :=
SUM ( Sales[Revenue] )
Prior Year Revenue :=
VAR PriorPeriodDates =
    SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        [Total Revenue],
        PriorPeriodDates
    )
YoY Revenue Amount :=
VAR CurrentRevenue = [Total Revenue]
VAR PriorRevenue = [Prior Year Revenue]
RETURN
    CurrentRevenue - PriorRevenue
YoY Revenue Growth % :=
VAR CurrentRevenue = [Total Revenue]
VAR PriorRevenue = [Prior Year Revenue]
RETURN
    DIVIDE (
        CurrentRevenue - PriorRevenue,
        PriorRevenue
    )

SAMEPERIODLASTYEAR moves the dates in the current filter context to the corresponding dates in the previous year. CALCULATE then evaluates [Total Revenue] against those shifted dates. DIVIDE returns blank when prior-year revenue is zero or unavailable.

For an alternative shift, use:

Prior Year Revenue :=
VAR ShiftedDates =
    DATEADD ( 'Calendar'[Date], -1, YEAR )
RETURN
    CALCULATE (
        [Total Revenue],
        ShiftedDates
    )

Business Example

Assume the report filters Calendar[Date] from January 1, 2025 through February 15, 2025.

Sales[Date] Sales[Revenue]
2024-01-15 100
2024-02-15 120
2025-01-15 140
2025-02-15 180

The expected measure results are:

Measure Result
Total Revenue 320
Prior Year Revenue 220
YoY Revenue Amount 100
YoY Revenue Growth % 45.45%

The calculation is:

(320 - 220) / 220 = 45.45%

When these measures appear in a matrix with Calendar[Year] on rows, each year is compared with its corresponding previous year.

Reporting and Visual Design

  • Add [Total Revenue] and [Prior Year Revenue] to a clustered column or line chart to show both periods.

  • Add [YoY Revenue Growth %] to a card or matrix and format it as a percentage.

  • Use conditional formatting to highlight positive and negative growth without relying only on color.

  • Include the absolute amount and percentage together; a large percentage from a small base can otherwise be misleading.

  • Keep date filtering on the Calendar table so the relationship propagates correctly to Sales.

Best Practices and Common Pitfalls

  • Prefer measures to calculated columns: Calculated columns evaluate in row context and do not dynamically respond to report filters.

  • Use one Calendar table: Multiple date tables can produce inconsistent YoY results and relationship ambiguity.

  • Avoid bidirectional relationships by default: They can introduce ambiguity and reduce query performance.

  • Check context behavior: A prior-year measure is meaningful when the visual supplies a year or date context. Test it with slicers and matrix drill-down.

  • Handle incomplete periods carefully: When comparing year-to-date results, ensure both periods contain comparable dates.

  • Optimize the source: For large DirectQuery models, partition and index source data, limit unnecessary columns, and avoid expensive row-by-row iterators.

33

How do you create a running total with DAX in Power BI?

A running total is a cumulative measure that calculates the total sales from the beginning of a period through the current date or category. In Power BI, it is best implemented as a measure using CALCULATE to define the relevant date range.

Read full answer

Applies toMicrosoft Power BI Desktop (current release) and Power BI Service

How It Works

The measure needs two components:

  • Current date: MAX('Date'[Date]) identifies the latest date available in the current row context. A row with January 2025 receives the latest date within January.

  • Cumulative date filter: CALCULATE removes existing date filters with REMOVEFILTERS('Date') and reapplies a filter for dates less than or equal to the current date.

CALCULATE changes the filter context in which [Total Sales] is evaluated. Therefore, each visual row displays the total accumulated up to that row’s date.

Use a dedicated date table rather than filtering directly from the sales table. This supports time hierarchies, year-to-date calculations, fiscal years, and consistent month-end behavior.

Data Model and Sample Data

Use a star schema with one row per day in the date table:

Date table

Date
2025-01-01
2025-01-02
2025-01-03

Sales table

Date Sales Amount
2025-01-01 100
2025-01-02 50
2025-01-03 25

Create a one-to-many relationship:

'Date'[Date] → Sales'[Date]

The relationship should normally use single-direction filtering from the date table to the sales table.

Code Example

First, create the base measure:

Total Sales :=
SUM ( Sales[Sales Amount] )

Then create the running total:

Running Total Sales :=
VAR CurrentDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        [Total Sales],
        REMOVEFILTERS ( 'Date' ),
        'Date'[Date] <= CurrentDate
    )

CurrentDate is calculated in the row context created by the visual. CALCULATE then evaluates [Total Sales] in a new filter context containing all dates up to and including that date.

If the running total should reset at the beginning of each year, use a year-to-date version:

Running Total Sales YTD :=
VAR CurrentDate =
    MAX ( 'Date'[Date] )
VAR CurrentYear =
    YEAR ( CurrentDate )
RETURN
    CALCULATE (
        [Total Sales],
        REMOVEFILTERS ( 'Date' ),
        'Date'[Date] <= CurrentDate,
        YEAR ( 'Date'[Date] ) = CurrentYear
    )

Expected Result

Using the running total measure with a visual containing Date and Total Sales produces:

Date Total Sales Running Total Sales
2025-01-01 100 100
2025-01-02 50 150
2025-01-03 25 175
Total 175 175

On January 2, the measure includes the 100 from January 1 and the 50 from January 2. On January 3, it includes all three transactions.

Best Practices and Common Pitfalls

  • Use a measure, not a calculated column: A measure responds dynamically to slicers, filters, and visual interactions. A calculated column stores one static result for each row.

  • Use a complete date table: Include every day in the reporting period and mark the table as a date table. This prevents missing dates and supports time-intelligence functions.

  • Choose the reset scope deliberately: Decide whether the calculation should accumulate from the beginning of the dataset, each year, each quarter, or each month. Remove the relevant filters and explicitly reapply the desired boundaries.

  • Keep the relationship simple: Normally use single-direction filtering from Date to Sales. Bidirectional filtering can introduce ambiguity and unnecessary performance overhead.

  • Format the visual deliberately: For daily trends, use a line chart sorted by Date. Show the latest cumulative value as a card or KPI rather than summing the running-total measure into another cumulative calculation.

  • Avoid duplicated date calculations: Centralize date logic in the date table instead of storing separate month, year, or weekday columns in the fact table.

34

How do you calculate a moving average in Power BI using DAX?

A moving average calculates an average over a fixed, trailing period, such as the current month plus the previous two months. In Power BI, implement it as a measure so the calculation responds dynamically to filters, relationships, and row-level security.

Read full answer

Applies toPower BI Desktop and Power BI Service (current release)

Moving Average Logic

The following measure calculates a three-month trailing moving average of monthly sales:

  1. MAX('Date'[Date]) identifies the latest date in the current filter context.

  2. CALCULATETABLE removes existing date filters and replaces them with a three-calendar-month window.

  3. SUMMARIZE groups the period into monthly totals.

  4. AVERAGEX averages those monthly totals.

Other filters, such as product, region, or customer, remain active. Only filters on the Date table are replaced.

Data Model Requirements

Use a proper star schema:

  • Date table: One row per calendar day, including dates without transactions.

  • Fact table: Contains Amount and a foreign key such as DateKey.

  • Relationship: Date[Date] has a one-to-many relationship with FactSales[DateKey].

  • Date table: Mark it as the date table in Power BI Desktop.

  • MonthStart: Create this calculated column on the Date table to support monthly grouping.

Using a separate date dimension ensures that empty months still exist and that time-intelligence functions behave correctly.

Code Example

Create the following measures and calculated column in Power BI Desktop.

// Calculated column on the Date table
MonthStart =
DATE ( YEAR ( 'Date'[Date] ), MONTH ( 'Date'[Date] ), 1 )
// Base measure
Total Sales =
SUM ( 'FactSales'[Amount] )
// Trailing average for the current month and previous two months
3-Month Moving Average =
VAR LastDate =
    MAX ( 'Date'[Date] )
VAR FirstMonthStart =
    DATE ( YEAR ( LastDate ), MONTH ( LastDate ) - 2, 1 )
VAR MonthlyTotals =
    CALCULATETABLE (
        SUMMARIZE (
            'Date',
            'Date'[MonthStart],
            "Total", COALESCE ( [Total Sales], 0 )
        ),
        REMOVEFILTERS ( 'Date' ),
        'Date'[Date] >= FirstMonthStart,
        'Date'[Date] <= LastDate
    )
RETURN
    AVERAGEX ( MonthlyTotals, [Total] )

COALESCE([Total Sales], 0) treats a month with no sales as zero. If months without sales should be excluded from the average, use [Total Sales] instead; AVERAGEX ignores blank values.

Sample Data and Expected Result

Assume the date table covers November 2024 through April 2025, and the fact table contains:

Transaction Date Amount
2025-01-10 100
2025-01-20 50
2025-02-05 200
2025-03-15 300
2025-04-01 120
2025-04-12 80

The monthly totals and moving averages are:

Month Monthly Sales 3-Month Moving Average
Nov 2024 0 0.00
Dec 2024 0 0.00
Jan 2025 150 50.00
Feb 2025 200 116.67
Mar 2025 300 216.67
Apr 2025 200 233.33

For example, April uses February, March, and April: (200 + 300 + 200) / 3 = 233.33.

Performance and Design Best Practices

  • Use a measure: A calculated column would compute one value per row and usually perform worse than a measure.

  • Keep the date table complete: Include every calendar day so missing months are represented correctly.

  • Use one consistent grain: Clearly define whether the moving average is based on days, weeks, or months.

  • Optimize large fact tables: Partition large sources, remove unnecessary columns, and use aggregation where appropriate.

  • Design the visual clearly: Use a line chart with Date[MonthStart] on the axis and [3-Month Moving Average] as the value. Format the measure as currency or a suitable number.

  • Label partial periods: The current month may contain only month-to-date sales, so display that context in the visual or tooltip.

35

How do you calculate a percentage of total using DAX?

A percentage of total in DAX divides a measure by a context-appropriate total, normally using DIVIDE. The denominator determines whether the result represents a grand total, a total at a selected visual level, or another comparison basis.

Read full answer

Applies toPower BI Desktop and Power BI Service (current release)

How It Works

Measures are evaluated in a filter context. In a visual containing product categories, for example, the filter context restricts [Total Sales Amount] to the current category.

A percentage calculation therefore needs:

  • Numerator: the measure in the current filter context.

  • Denominator: the same measure evaluated with the filters removed for the comparison.

  • Filter function: commonly ALL for an underlying grand total or ALLSELECTED for a total that retains selections outside the visual’s current row context.

In Power BI Desktop, select the model table where the measure should be created, choose Modeling > New measure, and enter the measure definition. Use Measure tools > Format > Percentage to format the result.

Choosing the Denominator

Requirement DAX approach Behaviour
Percentage of the underlying grand total ALL Removes the specified dimension-column filter while retaining other applicable filters
Percentage of a total at the selected visual level ALLSELECTED Ignores the current row context on the specified column while retaining relevant external selections
Percentage of a fixed table or column total Calculated column with CALCULATE and ALL Calculated during refresh and does not respond dynamically to slicers

ALL and ALLSELECTED are not interchangeable. Test ALLSELECTED carefully in matrices containing hierarchies, drilldowns, or multiple visual levels.

Code Example

The following example uses a star schema with one-to-many, single-direction relationships from DimProduct to FactSales:

Total Sales Amount =
SUM ( 'FactSales'[Sales Amount] )

% of Grand Total =
VAR Denominator =
    CALCULATE (
        [Total Sales Amount],
        ALL ( 'DimProduct'[Product Category] )
    )
RETURN
    DIVIDE (
        [Total Sales Amount],
        Denominator,
        0
    )

% of Selected Visual Total =
VAR Denominator =
    CALCULATE (
        [Total Sales Amount],
        ALLSELECTED ( 'DimProduct'[Product Category] )
    )
RETURN
    DIVIDE (
        [Total Sales Amount],
        Denominator,
        0
    )

The third argument to DIVIDE explicitly returns 0 when the denominator is zero. Without that argument, DIVIDE returns BLANK().

Business Example

Sample dimension data:

Product Key Product Category
101 Furniture
102 Electronics

Sample fact data:

Sales ID Product Key Units Sold Sales Amount
1 101 120 12,000
2 101 80 16,000
3 102 50 25,000
4 102 100 30,000

With DimProduct[Product Key] related one-to-many to FactSales[Product Key], a table visual containing DimProduct[Product Category] and the measures produces:

Product Category Total Sales Amount % of Grand Total
Furniture 28,000 33.73%
Electronics 55,000 66.27%
Grand Total 83,000 100.00%

The results are:

  • Furniture: 28,000 / 83,000 = 33.73%

  • Electronics: 55,000 / 83,000 = 66.27%

Best Practices and Pitfalls

  • Prefer measures: They respond correctly when users change dates, regions, categories, or other filter context. Calculated columns are evaluated during refresh and are unsuitable when the denominator must react dynamically.

  • Remove only necessary filters: Use the narrowest dimension column or table required. A broad ALL can remove important context and increase processing work.

  • Use a star schema: Keep facts separate from dimensions, define relationships with the intended cardinality and filter direction, and place measures in a dedicated measures table or another logically appropriate model table.

  • Use DIVIDE: It safely handles a zero denominator and supports an explicit alternative result.

  • Format consistently: Apply percentage formatting rather than multiplying the measure by 100.

  • Design for clarity: Use a sorted bar chart when comparing category contribution, label percentages clearly, and title the visual to state whether the denominator is the grand total or the selected total.

  • Test the filter context: Verify slicers, visual filters, hierarchies, drilldowns, and cross-highlighting produce the intended denominator.

36

What is the difference between drill-down and drill-through in Power BI?

In Power BI, drill-down expands a visual within the same report page, while drill-through navigates to a separate page with filters based on the selected item. Both help users move from summary information to more detailed analysis.

Read full answer

Applies toPower BI Desktop (current release) and Power BI Service

Core Difference

Feature Drill-down Drill-through
Navigation Expands a visual on the same page Opens a separate report page
Filtering Changes the level of detail displayed Passes filter context to the destination page
Typical use Category → Subcategory → Product Open a detailed customer, product or transaction page
Configuration Hierarchy or drill-down fields in a visual Drill-through fields and a destination page
Result More rows, columns or hierarchy levels A purpose-built page containing relevant analysis

Configuring Drill-Down

  1. In Model view, create a hierarchy from related dimension columns.

  2. Select the relevant fields in the data pane, right-click, and select Create hierarchy.

  3. Rename the hierarchy and arrange its levels from general to specific.

  4. Add the hierarchy to an axis in a visual.

  5. Use the Expand control to move to the next level.

For date hierarchies, use a dedicated date table and connect it to the fact table with an active relationship. Mark the date table with Table tools > Mark as date table so Power BI can generate the appropriate year, quarter, month and day hierarchy.

Users can normally drill down with the visual’s expand control and return using the up-arrow control. Expand all displays all available levels together.

Configuring Drill-Through

  1. Create a dedicated detail page and name it clearly, such as Product Details.

  2. Add a field such as Sales[Product] to Drill through fields in the Build pane.

  3. Add the required visuals to the page, including a title that identifies the selected product.

  4. In the source visual, right-click and select Drill through > Product Details.

  5. Test the navigation from the source visual or add a button with a Page navigation action.

When a user selects an item, the selected value becomes a filter on the destination page. The Keep all filters setting can control whether additional filters from the source visual are retained, so test this behavior when the destination depends on report-level, visual-level or row-level-security filters.

Business Example

Assume the following table contains sales data:

Category Subcategory Product Sales Amount
Electronics Computers Pro Laptop 500
Electronics Monitors 27-inch Monitor 300
Accessories Input Devices Wireless Mouse 120
Accessories Input Devices Keyboard 80

A category chart initially displays:

Category Net Sales
Electronics 800
Accessories 200
Total 1,000

With a Category > Subcategory > Product hierarchy, drilling into Accessories shows Input Devices = 200. Drilling down again shows Wireless Mouse = 120 and Keyboard = 80.

If the user drills through from the Wireless Mouse bar, the Product Details page opens with Wireless Mouse applied as a filter. Its total is therefore 120, and only records for that product appear.

DAX and Model Behavior

Use a measure so the values respond correctly to the active filter context. For example, a simple Net Sales measure defined as SUM('Sales'[Sales Amount]) will recalculate for each drill-down level because each level changes the visual’s filter context. A calculated column is generally less appropriate for this dynamic aggregation because it is evaluated during data refresh rather than recalculated for each visual filter context.

Best Practices and Common Pitfalls

  • Use a star schema, with sales stored in a fact table and categories, products and dates stored in dimensions.

  • Keep drill hierarchies intuitive; Category > Subcategory > Product is clearer than a technically correct but unfamiliar order.

  • Show context with titles, breadcrumbs, drill-through fields and a clear back or navigation button.

  • Avoid placing too many products, dates or other high-cardinality levels in one expandable visual.

  • Use Power BI Performance Analyzer to identify slow visuals before publishing.

  • In Power BI Service, verify drill-through permissions and test the interaction with the intended filters, row-level security and user permissions.

37

How are bookmarks and the Selection pane used in Power BI?

Bookmarks save and restore report-page state, including the page, filters, cross-highlighting, and visual interactions. The Selection pane manages report objects by showing or hiding, renaming, grouping, and reordering them.

Read full answer

Applies toMicrosoft Power BI Desktop (current release) and Microsoft Power BI Service (current release)

Bookmarks and Selection pane compared

Feature Main purpose Typical use
Bookmarks Save meaningful report states or navigation targets Reset filters, preserve a management view, or return to a previous dashboard page
Selection pane Control the visibility and visual hierarchy of report objects Hide background visuals, position titles and organize related visuals
Report interactions Define how one visual filters or highlights another visual Clicking a region highlights its sales in the revenue chart

The Selection pane is separate from the Format your visual pane and the visual Interactions pane. It manages report objects rather than DAX formatting or cross-filter behaviour.

Using the Selection pane

  1. Open View > Selection pane in Power BI Desktop.

  2. Select an object in the pane to highlight its corresponding visual on the canvas.

  3. Drag objects upward to bring them in front of other objects.

  4. Drag them downward to send them behind other objects.

  5. Use the eye icon to hide an object from report consumers.

Grouping related objects can make a report easier to maintain. For example, group a KPI card, supporting chart, and explanatory text under a name such as Revenue Overview.

In Power BI Service, report authors with edit permission can use the corresponding View > Selection pane and View > Bookmarks commands in report Edit mode. Read-only consumers do not receive these editing panes; they use the report’s configured interactions, buttons, and bookmark actions.

Hiding an object is only a report-design operation. It does not secure the underlying data or replace Row-level security (RLS).

Creating and using bookmarks

  1. Configure the report state, such as slicer selections or a visual selection state.

  2. Open View > Bookmarks to display the Bookmarks pane.

  3. Select Add and give the bookmark a descriptive name, such as East Region - Widgets.

  4. To apply it, select the bookmark in the Bookmarks pane.

  5. To add navigation, insert a button through Insert > Buttons, then in the formatting pane select Action > Type: Bookmark and choose the required bookmark.

Bookmarks support common dashboard actions:

  • Default state: Set a bookmark as the report’s default state so the report opens with its saved page and filters.

  • Reset action: Clear the required filters and visual states, then save that configuration as a reset bookmark.

  • Navigation: Apply a bookmark saved on another page to return users to that page and its saved state.

  • Guided analysis: Move users through sales, product, and regional views without manually changing every filter.

Bookmarks do not redefine DAX calculations. However, restoring their filters changes the measure’s filter context and can therefore change its result.

Business example

Assume a sales table contains:

Region Product Revenue
East Widget 50,000
East Gadget 25,000
West Widget 75,000
West Gadget 30,000

The analyst creates these slicer selections:

View Selected filters Expected revenue
All sales No filters 180,000
East region Region = East 75,000
East widgets Region = East, Product = Widget 50,000

The analyst saves the last configuration as the East Widgets bookmark. A button on the same report page can then apply it, showing 50,000 instead of the default 180,000.

Code Example

A measure can display the revenue in the current filter context. When a bookmark restores the slicer selections, the measure is recalculated for that restored context.

// Evaluated in the current filter context, including restored slicer selections.
Selected Revenue :=
VAR RevenueAmount = SUM ( 'Sales'[Revenue] )
RETURN
    RevenueAmount

With the sample data, the measure returns 180,000 for all sales, 75,000 for East, and 50,000 for East widgets. The bookmark stores the report state rather than a separate DAX expression.

Best practices and common pitfalls

  • Use meaningful names: Prefer East Region - Widgets over Bookmark 3.

  • Create reset bookmarks intentionally: Clear the required filters, cross-highlighting, and page state before saving it; a bookmark is not an automatic reset command.

  • Test bookmark behaviour: Verify page navigation, slicer selections, visual states, and results after publishing, refreshing, and testing each RLS role.

  • Avoid excessive bookmarks: Create bookmarks for meaningful business views rather than every filter combination.

  • Keep the canvas organised: Use the Selection pane to give titles, backgrounds, and related visuals a predictable hierarchy.

  • Do not rely on hidden visuals for security: Hidden visuals are not a security boundary; use RLS or other supported authorization controls.

  • Test sensitivity labels: Bookmarks do not bypass RLS, and measures remain constrained by the signed-in user’s RLS role. A restored view may still be sensitive, so apply and test Microsoft Purview sensitivity labelling as required.

38

What is a Power BI tooltip page?

A Power BI tooltip page is a report page used to display richer contextual information when a user hovers over a supported visual. It can show focused values, breakdowns, trends, and supporting visuals using the hovered data point’s context from the source visual.

Read full answer

Applies toPower BI Desktop and Power BI Service (current release)

How It Works

  • A tooltip page is authored in Power BI Desktop and assigned to a specific visual through the visual’s formatting options.

  • When the user hovers over the source visual, Power BI renders the tooltip page using the hovered data point’s filter context.

  • Filters affecting the source visual, including applicable report, page, visual, and row-level security filters, are honored.

  • Unlike a standard visual tooltip, which mainly supports text and fields, a tooltip page can contain cards, tables, charts, and other visuals configured with fields and measures.

  • The page uses the report’s existing data model, relationships, refresh process, and measures; it does not create a separate dataset.

  • A tooltip page can remain visible as a report tab. Right-click its page tab and select Hide when it should be accessible only through hover interactions.

How to Create One

  1. Add a blank report page and give it a meaningful name, such as Regional Sales Tooltip.

  2. On the Page tools ribbon, use Page size to set the canvas type to Tooltip.

  3. Add the cards, tables, charts, fields, and measures that should appear in the tooltip.

  4. Select the source visual on the main report page.

  5. Open Format visual > General > Report page tooltip, select Add, and choose the tooltip page.

  6. Test the hover interaction in Power BI Desktop, publish it to Power BI Service, and verify its behavior for users with different row-level security roles.

Business Example

Assume the Sales table contains one row for each region-product combination:

Region Product Sales Amount
East Widget 100
East Gadget 50
West Widget 80
West Gadget 20

Create a measure named Total Sales that sums Sales[Sales Amount]. On the main report page, create a matrix with Sales[Region] in Rows and [Total Sales] in Values.

Create a tooltip page containing:

  • A card using [Total Sales]

  • A table or bar chart with Sales[Product] and [Total Sales]

The hover supplies filter context for the hovered region. The card’s Total Sales measure is evaluated in that filter context. In the product table, each product row also has row context, so the visual evaluates Total Sales for the product in that row as well as for the hovered region.

Expected results:

Hovered Region Tooltip Total Sales Product Breakdown
East 150 Widget 100; Gadget 50
West 100 Widget 80; Gadget 20

Performance and Design Guidance

  • Keep tooltips focused: Show only information that helps explain the source visual. Avoid putting an entire dashboard into a tooltip.

  • Use measures for aggregations: Measures calculate dynamically under the relevant filter and row context. Calculated columns are computed during data refresh and are better for row-by-row logic.

  • Limit visual complexity: Tooltip visuals are rendered when the hover interaction opens, so expensive measures, large tables, and numerous visuals can make the interaction slow.

  • Use a star schema: Connect fact and dimension tables with well-defined relationships, and avoid many-to-many relationships unless a specialized modeling pattern is required.

  • Prefer clear visuals: Use a card for the headline value and a small table or bar chart for the breakdown. Label measures clearly and avoid unnecessary legends.

  • Test all states: Check empty categories, filtered visuals, hidden-page behavior, row-level security, and interactions in both Power BI Desktop and the published Service report.

  • Preserve accessibility: Tooltips supplement rather than replace essential labels. Important information should remain visible without hovering.

39

What is conditional formatting in Power BI and how do you configure it?

Conditional formatting in Power BI applies colors, icons, or data bars according to the values displayed by a visual. It is a report-design capability rather than a DAX feature, and its results respond to the visual’s current filters.

Read full answer

Applies toMicrosoft Power BI Desktop (current release), Power BI Service (current release), and Power BI Report Builder (current release)

Where It Lives

  • Table or matrix visual → Format visual → Conditional formatting: add rules for background color, font color, icon sets, or data bars.

  • Right-click a field in the field well → Conditional formatting: opens the rule builder for that column in a table or matrix.

  • Supported chart visuals: relevant options appear in the visual-specific format section; for example, use Data bars for a bar or column chart.

  • Cell elements, page-background settings, and report themes are separate formatting features. They do not dynamically react to data values.

Rule Types and Basis

Rule Purpose Typical use
Background color Changes a cell’s or data point’s background Highlight exceptions
Font color Changes label text Maintain contrast or flag status
Data bars Shows a proportional bar Compare magnitudes
Icon sets Shows icons such as traffic lights Communicate status or direction

Each rule can use either:

  • Value: fixed thresholds and selected colors.

  • Field value: a column or measure evaluated in the visual’s current filter context.

Worked Example

Sample Sales data:

Product Month Amount
Widget Jan 600
Gadget Jan 300
Widget Feb 900
Gadget Feb 450

For a table containing Product and a Total Sales measure:

  1. Under Conditional formatting, add a Background color rule using Field value = Total Sales.

  2. Configure >= 1000 as green, >= 500 as amber, and the remaining values as red.

  3. Add a Data bars rule using Field value = Total Sales, with a constant minimum of 0, constant maximum of 1500, and a green bar color.

Expected result:

Product Total Sales Data bar Background
Widget 1500 Full width Green
Gadget 750 50% of the configured range Amber

Code Example

Create the measure with variables:

Total Sales =
VAR _Amount = SUM ( Sales[Amount] )
RETURN
    _Amount

A measure can also return the color used by a field-based background-color rule:

Sales Status Color =
VAR _Total = [Total Sales]
RETURN
    SWITCH (
        TRUE (),
        _Total >= 1000, "#2E7D32",
        _Total >= 500,  "#F9A825",
                      "#C62828"
    )

Configure the background-color rule with Field value = Sales Status Color.

For an icon set based on year-over-year growth:

Sales YoY Growth % =
VAR _Current = [Total Sales]
VAR _Previous =
    CALCULATE (
        [Total Sales],
        SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
    )
RETURN
    IFERROR ( DIVIDE ( _Current - _Previous, _Previous ), BLANK () )

Mark Calendar as a date table, ensure it contains contiguous dates, and include Calendar[Date] or its hierarchy in the visual. Use Sales YoY Growth % as the icon set’s field value—for example, green above 0%, yellow from -10% through 0%, and red below -10%.

Best Practices and Pitfalls

  • Prefer measures for large datasets: measures evaluate in the current filter context, while calculated columns are calculated during refresh.

  • Consolidate dynamic logic: use a measure when thresholds or colors vary by product, region, or period instead of creating many hard-coded rules.

  • Keep rule counts modest: additional rules increase evaluation and maintenance work.

  • Remember that formatting is not security: conditional formatting presents data but does not protect it; use row-level security for access control.

  • Design for accessibility: use color-blind-safe palettes and combine color with icons or labels.

  • Account for Service permissions: users need edit rights to change formatting in the Service, and RLS affects the values evaluated by rules.

  • Paginated reports differ: Report Builder uses tablix-specific conditional-formatting rules based on report expressions or fields rather than the Desktop visual’s conditional-formatting pane.

40

How do Power BI reports and dashboards differ?

A Power BI report is an interactive analytical document, usually containing multiple pages for detailed analysis. A dashboard is a separate Power BI Service artifact designed primarily as a concise, visual summary of key metrics for monitoring and decision-making.

Read full answer

Applies toPower BI Desktop and Power BI Service (current release)

Core Differences

Aspect Report Dashboard
Primary purpose Explore and analyze data Monitor KPIs and communicate status
Creation Primarily in Power BI Desktop Created in Power BI Service
Structure Multiple pages with detailed visuals A collection of tiles, often arranged as one overview page
Interaction Slicers, drilldown, drillthrough, bookmarks, cross-filtering and tooltips Supports tile interactions and navigation, but is optimized for quick consumption
Audience Analysts, operational users and subject-matter experts Executives, managers and operational monitoring teams
Detail level Detailed and exploratory Summarized and focused on important metrics
Data access Users can often explore the underlying visuals and interact with filters Users see the summarized presentation without entering the full report

A report remains the analytical source behind many dashboard tiles. Adding a report page to a dashboard does not copy all of the report’s analysis into the dashboard; it presents the selected page as a tile. Individual report visuals cannot be added directly as standalone dashboard tiles.

Typical Use Cases

A report is appropriate when users need to:

  • Investigate trends, exceptions and contributing factors.

  • Filter a large dataset by date, region, product or customer.

  • Use drillthrough to move from a summary visual to supporting detail.

  • Navigate between related pages using bookmarks or buttons.

  • Provide calculations, definitions and supporting context through report-page tooltips.

A dashboard is appropriate when users need to:

  • Monitor sales, revenue, budgets, service levels or operational targets.

  • Review a small set of KPIs from multiple reports.

  • Receive a focused executive overview without performing extensive exploration.

  • Display status on a wall screen, browser or mobile device.

Dashboards are not necessarily static. Their tiles can interact, and a report-page tile can retain the interactions configured in that report. However, the detailed exploration and navigation remain in the source report.

Business Example

Assume an Orders table contains:

Month Region Revenue Target
January East 120,000 130,000
January West 90,000 110,000
February East 140,000 150,000
February West 100,000 110,000

A sales performance report could contain:

  • A page with a monthly revenue trend and regional comparison.

  • A slicer for region and product category.

  • A drillthrough page showing individual orders.

  • Tooltips containing profit margin, growth and prior-year results.

A sales overview dashboard could display tiles based on report pages showing:

  • Total revenue: 450,000.

  • Total target: 500,000.

  • Target attainment: 90%.

  • Revenue versus target.

  • Regional performance and monthly trends.

  • A traffic-light visual identifying the West region as below target.

The report provides the detailed exploration, while the dashboard communicates the current performance at a glance.

Creating and Publishing

  1. Build the interactive report in Power BI Desktop using a properly modelled dataset.

  2. Create measures for reusable business logic rather than hard-coding totals in visuals.

  3. Select File > Publish to Power BI and choose the destination workspace.

  4. Open the workspace in Power BI Service, select Dashboards, and create a new dashboard.

  5. Add relevant report pages as tiles and arrange them around the main KPIs.

  6. Configure tile interactions, text, branding and navigation as required.

  7. Share the dashboard through workspace permissions, an app, or a governed sharing method.

When the underlying report is updated and republished, its dashboard tiles reflect the updated report version. The dashboard should still be reviewed because its visuals, drillthrough behavior and explanatory content may need adjustment.

Best Practices and Common Pitfalls

  • Keep the dashboard focused: Show only metrics that support a specific decision. Move investigation into the linked report.

  • Maintain clear metric definitions: Use consistent labels, time periods, currencies and aggregation rules across reports and dashboards.

  • Design for scanning: Place the most important KPI in a prominent position, use meaningful colors and avoid unnecessary decoration.

  • Model the data first: Use a star schema, active relationships, appropriate cardinality and filter direction, and reusable DAX measures.

  • Optimize performance: Limit high-cardinality visuals, avoid unnecessary calculated columns and test every visual with realistic data volumes.

  • Apply security centrally: Implement row-level security in the semantic model so it applies consistently to reports and dashboard tiles.

  • Avoid duplicating logic: Define measures once in the model and reuse them in both report visuals and dashboard tiles.

  • Plan navigation deliberately: Link the dashboard to the relevant report page so users can move from summary to evidence without guessing where to click.

  • Treat dashboards as governed content: Review them after model or report changes; an updated tile may contain new visuals or behavior that no longer matches the original dashboard layout.

41

How do Power BI Service workspaces and apps differ?

Workspaces are collaboration and management areas for Power BI content, while apps are packaged end-user experiences created from a workspace. A workspace supports development, refresh and governance; an app controls how selected reports, dashboards and semantic models are distributed and accessed.

Read full answer

Applies toMicrosoft Power BI Service (current release)

Workspaces and Apps Compared

Aspect Workspace App
Primary purpose Develop, manage and collaborate on Power BI content Distribute a curated experience to business users
Typical owner Report developers, BI teams and data owners Business owners, product managers or BI teams
Content Reports, dashboards, semantic models, workbooks, paginated reports and other supported assets A selected collection of reports, dashboards, semantic models and other supported content
Access model Members receive workspace roles such as Admin, Member, Contributor or Viewer Users receive app access and can access content through My apps
Refresh Refresh is configured on datasets or dataflows in the workspace Apps do not define a separate refresh process; the underlying workspace assets retain their refresh configuration
Governance Supports development, testing, production and governance activities Provides a separate user-facing destination and audience while the content remains in the workspace

How Publishing Works

  1. Developers create or publish content to a workspace.

  2. A workspace contributor, member or administrator selects the content that should be included in the app.

  3. From the workspace’s More options (…) menu, the publisher selects Create app.

  4. In App settings, they configure the app name, description, icon, audience and content-exploration permission.

  5. The publisher publishes the app, after which authorized users find it under My apps.

  6. When the underlying content changes, users receive the updated experience without needing direct access to every workspace asset.

A workspace is therefore the working environment, while an app is the controlled delivery mechanism. A semantic model can support both internal workspace use and a business-facing app.

Permissions and Security

  • Workspace roles determine what members can do inside a workspace:

  • Admin: Manage the workspace, members, apps, settings and content.

  • Member: Publish and update supported content, share the workspace and create apps.

  • Contributor: Create and update content, create apps and publish content, without managing members or workspace settings.

  • Viewer: View permitted workspace content.

  • App access determines who can open and consume the packaged experience.

  • An app user does not need workspace Viewer access merely to use content through the app.

  • An individual may have both workspace membership and app access; these are separate access paths, and app access does not itself grant workspace membership.

  • The Users can explore app content setting controls whether app users can explore included content and view underlying semantic-model data. When disabled, their experience is limited to viewing content within the app.

  • Row-level security, object-level security and Microsoft Entra ID groups remain important for protecting data inside the underlying semantic model.

Apps should expose only the content required for the intended audience. For example, executives may receive a sales-performance app, while the wider team uses a separate operational workspace.

Business Example

Assume a retail organization creates a semantic model named Sales Model with these monthly results:

Month Region Revenue
January North 120,000
January South 95,000
February North 140,000
February South 110,000

The analyst publishes Sales Model and a Regional Sales report to the Sales Development workspace. After regional targets and definitions are approved, the BI team creates a Regional Sales Performance app containing those assets.

The expected result is:

  • Developers and analysts continue working in the Sales Development workspace.

  • Regional managers access the approved report through My apps.

  • Scheduled refreshes continue on Sales Model within the workspace.

  • The audience sees a focused experience rather than every development report in the workspace.

Best Practices and Common Pitfalls

  • Separate development and production: Use development, testing and production workspaces for substantial deployments, preferably supported by deployment pipelines.

  • Curate app content carefully: Do not include every workspace report. Give each app a clear business purpose and audience.

  • Manage dependencies: Reports and semantic models required by the app must be included or remain available through the correct publishing process.

  • Plan refresh ownership: Assign responsibility for refresh failures, source credentials, gateway availability and capacity.

  • Use meaningful names: Describe the business purpose and reporting period, such as Finance Monthly Close App, rather than a vague name such as Finance App.

  • Control sharing separately: Workspace membership supports collaboration, while app audiences control the consumption experience.

  • Design for end users: Keep navigation task-oriented, use consistent terminology and avoid exposing development-oriented measures or tables.

  • Test permissions: Validate app access, content-exploration settings, row-level security and gateway behavior using representative users before publishing the app.

42

How do you share Power BI reports securely?

Secure Power BI sharing combines least-privilege access through workspaces or apps with row-level security (RLS) for data restrictions. Use Microsoft Entra ID identities and groups, publish to a governed workspace, and distribute reports through Power BI Service rather than unsecured .pbix files.

Read full answer

Applies toMicrosoft Power BI Desktop and Power BI Service (current release)

Secure Publishing and Access

  1. Publish the semantic model and report

  2. In Power BI Desktop, select Publish or File > Publish to Power BI.

  3. Choose a governed target workspace.

  4. Publishing creates the semantic model as a dataset and the report in Power BI Service, where access, refresh, monitoring, and governance can be managed.

  5. Assign least-privileged access

  6. Open the workspace and select Manage access.

  7. Add Microsoft Entra ID users or, preferably, groups.

  8. Assign the workspace role that matches each person’s responsibilities.

Workspace role Typical use
Viewer View and interact with reports
Contributor Create, update, and delete workspace items
Member Publish and update content and manage operational features such as alerts
Admin Manage workspace settings, members, gateways, and deployment pipelines

Workspace roles control permitted actions in the workspace; they do not automatically filter individual rows.

Distributing Through Apps

  • Create an app from Apps > New app in Power BI Service.

  • Add the required reports and configure the app audience for Entra ID users, groups, or the organization.

  • Users can consume the app without receiving broad browsing access to unrelated workspace content.

  • Use apps when consistent branding, controlled release, and clear ownership are important.

  • A direct report link can be used for small, low-risk audiences, but governed workspace or app distribution provides better lifecycle and permission management.

Restricting Data with RLS and OLS

  • Create an RLS role in Power BI Desktop with Modeling > Manage roles.

  • Use Add table filter > Advanced filter to define the rule.

  • For dynamic security, combine a table containing each user’s Entra UPN and role with a UPN-based DAX expression.

  • RLS is saved with the semantic model and enforced by Power BI Service after publishing; users are not separately assigned the desktop role.

  • For a UPN-based rule, test the intended identities in Power BI Service with Security > Test as role. Desktop View as role does not reliably emulate another user’s UPN.

  • Use object-level security (OLS) when selected tables must be unavailable. OLS is table-level rather than column-level and does not redact values already exposed in visuals. To restrict an individual column, separate it into its own table and ensure restricted users cannot see visuals based on it, or enforce controls in the source database.

Code Example

Suppose Role Assignment contains one unique row per role:

RoleName Email
Sales ana@contoso.com
Finance sam@contoso.com

And Orders contains:

OrderID RoleName Amount
101 Sales 100
102 Finance 200
103 Sales 50

Model Orders as the many side and Role Assignment as the one side of a many-to-one relationship on RoleName. Keep Role Assignment[RoleName] unique and use single-direction filtering from Role Assignment to Orders.

Create an RLS role such as Dynamic RLS and apply this advanced filter to Role Assignment:

VAR CurrentEmail = USERPRINCIPALNAME()
RETURN
    'Role Assignment'[Email] = CurrentEmail

The filtered assignment propagates its RoleName through the relationship:

  • ana@contoso.com sees orders 101 and 103, totaling 150.

  • sam@contoso.com sees order 102, totaling 200.

  • A user without an assignment sees no order rows.

For multiple users sharing roles, normalize users, roles, and user-role assignments into separate tables and validate that the chosen relationships propagate filters as intended.

Best Practices and Common Pitfalls

  • Prefer Entra ID groups: Manage group membership centrally instead of repeatedly assigning individuals.

  • Separate publishing from consumption: Give business users Viewer access and reserve Contributor, Member, and Admin roles for content owners.

  • Test identities and relationships: Verify RLS with every intended user type in Power BI Service and confirm relationship direction and cardinality.

  • Plan privileged access: Review dataset ownership, exports, gateways, and source access separately; RLS is not a substitute for source-system controls.

  • Protect sensitive content: Apply sensitivity labels, certification, and OLS where appropriate; never rely on visual formatting to hide confidential data.

  • Avoid insecure distribution: Do not email .pbix files, share personal credentials, or use Publish to web for confidential reports.

  • Secure refreshes: Use an appropriately governed gateway and centrally managed credentials rather than embedding personal login details.

  • Review access regularly: Remove stale permissions, monitor workspace activity, and maintain clear workspace ownership.

43

What is row-level security in Power BI?

Row-level security (RLS) restricts the rows a user can see in a Power BI dataset. It works by assigning users or groups to security roles, with each role containing a DAX filter that determines which rows are visible.

Read full answer

Applies toPower BI Desktop (current release) and Power BI Service (current release)

How RLS Works

RLS is enforced at query time; it does not delete or physically separate data.

  • Role: A named security group defined in the semantic model.

  • Member: A user or Entra group assigned to that role.

  • Security filter: A DAX expression that returns TRUE for permitted rows and FALSE for other rows.

  • Fixed RLS: Every member of a role sees the same rows, such as all sales from the East region.

  • Dynamic RLS: Each user’s identity is evaluated against a mapping table, allowing different users to see different rows. For example, a User Access table maps each user’s UPN to one or more regions; the role’s DAX filter uses USERPRINCIPALNAME() to look up the allowed regions and returns TRUE only for rows in those regions.

RLS applies to queries, visuals, drillthrough, exports, and totals generated from the protected dataset. Users with appropriate workspace or app access can consume the content without seeing unauthorized rows.

Implement RLS in Power BI Desktop

  1. Open the .pbix file and select Modeling > Manage roles.

  2. Select Add and enter a role name, such as East Sales Users.

  3. Select the model table that should be filtered.

  4. Enter a Boolean DAX expression in the table filter box.

  5. Select OK, then save the model.

For a fixed regional role, use the expression 'Sales'[Region] = "East". This allows members of the role to see only Sales rows where Region is East.

In a production star schema, apply the filter to a dimension such as Region rather than directly to the fact table. The filter then propagates through the one-to-many relationship to the related sales rows.

Publish and Assign Security Roles

After creating the role in Power BI Desktop:

  1. Publish the dataset to a Power BI workspace.

  2. In Power BI Service, open the workspace and select Settings > Row-level security.

  3. Select the role and choose Add.

  4. Add the user or Entra group as a role member.

  5. Give users access through an app or grant them the Viewer workspace role.

Test with representative accounts in the Service, not only in Desktop. Use Modeling > View as in Desktop to validate fixed roles, and use service impersonation or dedicated test identities for dynamic roles.

Business Example and Expected Result

Given this simplified sales data:

Order ID Region Amount
1001 East 500
1002 West 300
1003 East 200
1004 South 150

And a User Access mapping table for dynamic RLS:

UserPrincipalName Region
ana@contoso.com East
ben@contoso.com West

Expected results with dynamic RLS:

User Visible Orders Visible Amount
ana@contoso.com 1001, 1003 700
ben@contoso.com 1002 300

A visual total therefore displays 700 for the first user and 300 for the second user rather than exposing the combined dataset total.

Best Practices and Common Pitfalls

  • Use a star schema: Apply RLS to a small dimension such as Region, Department, or Cost Center, then let relationships restrict the fact table.

  • Prefer simple relationship direction: Usually use single-direction filtering from security dimensions to facts. Bidirectional relationships can unintentionally broaden RLS filters.

  • Protect the mapping table: Restrict editing permissions so users cannot assign themselves access to additional regions.

  • Do not use RLS expressions inside measures: Define access in model roles; measures should calculate business results over the already filtered context.

  • Avoid excessive granularity: Filtering by very high-cardinality tables can increase query complexity and reduce report performance.

  • Validate totals and interactions: Check drillthrough, hierarchies, slicers, exports, and measures that use CALCULATE to ensure they respect the same RLS boundary.

  • Use least privilege: Provide Viewer or app access rather than edit access, since privileged dataset access can expose broader data and weaken the intended security model.

44

How does dynamic row-level security work in Power BI?

Dynamic row-level security (RLS) uses a role-based DAX rule to filter semantic-model rows according to the signed-in identity, so members of one role can see different rows. The role must still be assigned to each user or security group; dynamic filtering changes row access within a role, but it does not replace role assignment.

Read full answer

Applies toPower BI Desktop and Power BI Service (current release)

How It Works

Power BI evaluates the RLS expression as a filter on the secured table. The expression is evaluated for each row: only rows for which the expression returns TRUE remain visible.

A common dynamic expression identifies the signed-in user with USERPRINCIPALNAME() and compares that identity with an email in a security mapping table. USEROBJECTID() can instead be used when the model stores Microsoft Entra object IDs.

For example, suppose the mapping table contains:

Email Allowed Region
alex@contoso.com North
priya@contoso.com South
sam@contoso.com North

A customer dimension contains:

CustomerKey Customer Region
101 Acme Ltd North
102 Contoso Inc. South
103 Fabrikam North

When alex@contoso.com opens the report, the security rule allows only DimCustomer rows in the North region. In a standard star schema, the relationship from DimCustomer to the sales fact table then propagates that restriction to sales rows. The expected result is:

Signed-in user Visible customer keys Visible region
alex@contoso.com 101, 103 North
priya@contoso.com 102 South
sam@contoso.com 101, 103 North

Role and Model Setup

  1. Import the security mapping table into the model.

  2. Decide whether it should be a standalone lookup or part of a security-dimension design. A self-contained RLS expression can query the mapping table without a relationship; relationships should be added deliberately when they are needed for propagation.

  3. In Power BI Desktop, go to Modeling > Manage roles and select Add.

  4. Create a role, such as Region User, and select the table whose rows should be filtered.

  5. Select Advanced > Dynamic and enter a DAX expression that returns TRUE for permitted rows.

  6. Test the role before publishing the dataset and report.

For multiple permitted values per user, represent the mapping as a one-to-many relationship between the user and access values. A bridge or security dimension is often clearer than a large comma-separated list.

Testing in Desktop and Service

In Power BI Desktop, use Modeling > View as role, select the role, and use Test as to choose the user to simulate. The selected identity must match the value stored in the mapping table.

In Power BI Service:

  • Publish the updated dataset, report, and role definition.

  • Assign the RLS role to individual users or security groups through the semantic model’s permissions.

  • Grant the users or groups only the required workspace, app, and dataset access.

  • Verify the result using the service’s role-preview facility or an assigned test account.

If a user can access the semantic model but is not assigned to an RLS role, that role’s filters do not apply. Assigning a group to an app also does not automatically assign the RLS role; the relevant group must be assigned to the role as well.

Best Practices and Common Pitfalls

  • Filter dimension tables where possible: In a star schema, restrict a dimension and let its relationships filter facts. Keep relationship direction and cardinality appropriate.

  • Model the mapping deliberately: Keep it small, avoid unnecessary relationships, and enforce one logical access record per user and scope.

  • Refresh access data regularly: Missing or stale mappings can deny legitimate access or fail to revoke it.

  • Validate the identity format: USERPRINCIPALNAME() may differ for guests or other identity providers. Confirm the exact value available in the target environment.

  • Use least privilege: Do not grant unnecessary Build or higher permissions. RLS controls row visibility but is not a substitute for workspace, dataset, and app permissions.

  • Design multiple roles carefully: Filters from separate roles are additive—a user receives the union of their permitted rows. If two conditions must both apply, combine them in one role.

  • Consider model separation for strict tenants: For highly sensitive tenant isolation, combine well-designed RLS with restricted dataset access and, where justified, separate workspaces, apps, or semantic models.

45

What is object-level security in Power BI?

Object-level security (OLS) is a Power BI Service feature that restricts which objects — tables, columns, fields, and measures — a specific user or security group is allowed to see or query. It is the column- and table-level counterpart to row-level security (RLS), which restricts which rows a user can see. Together they let one published semantic model serve many audiences with different entitlements, without duplicating the dataset.

Read full answer

Applies toPower BI Service (current release)

OLS Compared with RLS

Aspect Object-level security (OLS) Row-level security (RLS)
Restricts Tables, fields, measures Rows of data
Configured in Power BI Service → Security → Roles Power BI Desktop → Manage roles (or Service)
Permission levels None, Read (metadata only), Read + data Row filter (DAX expression)
Dynamic support Static user/group assignment Dynamic via USERPRINCIPALNAME()
Applies to Imported, DirectQuery and composite models in the Service Same, plus Power BI Desktop

How OLS Works

  • Roles: You create a named role (for example Finance) and assign Entra ID users, groups, security groups or distribution lists to it.

  • Object permissions: For each table, column, field or measure inside the role you choose one of:

  • None — the object is invisible to members of the role.

  • Read — members can see the object’s name and metadata (e.g. in the field list) but get no data.

  • Read + data — members can see and query the object’s data.

  • Combined with RLS: RLS governs rows, OLS governs objects. A user in the Finance role may have Read + data on FactSales but only None on FactHR; the same model can simultaneously carry a dynamic RLS role that scopes FactSales to the user’s own region.

  • Permissions combine: If a user belongs to more than one role, access is the union of what those roles grant — the most permissive level wins.

Creating an OLS Role (menu path)

  1. In the Power BI Service, open the Workspace and select the semantic model (dataset).

  2. Choose ⋯ (More options) → Security → Roles → New role.

  3. Name the role and add members (users/groups) via Add.

  4. Expand Tables, Fields or Measures and set None / Read / Read + data for each object.

  5. Save, then publish the dataset and the report.

RLS roles are created with Modeling → Manage roles in Power BI Desktop and travel with the .pbix file; the desktop View as button simulates RLS only, so OLS must be tested with a real test account in a separate browser profile.

Limitations and Caveats

  • OLS is a Service-side feature: object permissions are managed on the published semantic model in the Service, not in Power BI Desktop.

  • Desktop cannot validate OLS: always verify with a test identity, because “View as” only simulates RLS.

  • Opaque visuals: visuals built on objects a user cannot read render as restricted or empty, so hide or design around them rather than relying on visual defaults.

  • Not a replacement for AS security: for Premium/Enterprise datasets with Azure Analysis Services backing, Analysis Services roles are the enterprise mechanism.

  • Personas and app audiences: app audiences and “View as persona” do not automatically grant OLS access.

Best Practices

  • Model for security: build a clean star schema, keep security dimensions (DimUser, DimRegion) separate from fact tables, and use one-to-many relationships with single-direction filtering so RLS is predictable.

  • Prefer dynamic RLS over many static roles: one role plus USERPRINCIPALNAME() scales to thousands of users; create additional OLS roles only for genuinely different object entitlements.

  • Least privilege: give None by default and open only what each audience needs; remember that adding a user to a second role can only increase access.

  • Document roles: keep a matrix of role → member group → object permissions in your model documentation so audits and onboarding are quick.

  • Test in production-like conditions: verify with test accounts in a separate browser session, check both the report and any embedded/copilot experiences, and re-test after each model change.

46

When is a Power BI gateway required?

A Power BI gateway is a bridge between the Power BI Service and data sources that the service cannot access directly, typically because the source is on a private network. It enables scheduled refreshes, DirectQuery, and live connections without exposing the source directly to the internet.

Read full answer

Applies toPower BI Desktop, Power BI Service, and Microsoft On-premises Data Gateway (current releases)

How the Gateway Works

The Microsoft On-premises Data Gateway runs on a machine inside the network containing the data source.

  • Outbound connection: The gateway normally maintains an outbound encrypted connection to the Power BI Service; inbound connectivity from the internet is not required.

  • Request routing: Power BI sends refresh or query requests through the gateway to the appropriate source.

  • Credential management: Source credentials are stored securely in the gateway rather than embedded in a report.

  • No data storage: The gateway transfers requests and results but does not act as a persistent data repository.

  • Desktop versus Service: Power BI Desktop can access an on-premises source directly without a gateway. A gateway becomes necessary when the Power BI Service must access that source over the private network.

When a Gateway Is Required

Scenario Gateway needed? Reason
Importing directly from an on-premises SQL Server in Power BI Desktop No Desktop accesses the source over the local network
Scheduled refresh of a published dataset backed by on-premises SQL Server Yes The Power BI Service cannot normally reach the private source directly
DirectQuery against a private SQL Server Yes Queries must pass through a host with network access to the source
Live connection from Power BI Service to an on-premises Analysis Services instance Yes The service needs a route to the Analysis Services server
Importing from Azure SQL Database No Power BI Service can access the public cloud endpoint directly
Importing an Excel workbook from OneDrive or SharePoint Online No These are cloud-accessible sources
Accessing a private data source through a supported direct network connection Sometimes The network configuration determines whether a gateway is needed

A personal gateway is intended for individual use. A standard on-premises gateway should be used for shared organizational workloads, multiple users, and gateway clustering.

Configuring and Using a Gateway

  1. Install the gateway on an always-on machine that can reach the source. The gateway does not have to run on the database server itself.

  2. Register it with Power BI using Settings and administration > Gateways in the Power BI Service.

  3. Create a gateway cluster for organizational deployments. Add one or more gateway instances to the cluster to improve availability.

  4. Store source credentials under the gateway’s data source settings. Use the service principal or another supported authentication method when appropriate for the source.

  5. Select the gateway in Power BI Desktop when creating the connection. For SQL Server, use Get Data > SQL Server, open Advanced options, and select the gateway or gateway cluster from the on-premises gateway option.

  6. Publish the report. If the workspace does not automatically receive the gateway assignment, open the workspace settings, select Data gateway settings, and choose the appropriate gateway cluster.

  7. Test the connection and refresh from the dataset before relying on a production refresh schedule.

The gateway must be able to resolve the source hostname, establish the required database port, and authenticate using the configured credentials.

Business Example

Suppose Contoso publishes a Power BI report based on an on-premises SQL Server.

Imported source data:

Region Revenue
East 50,000
West 75,000

The initial import works directly from Power BI Desktop. After publishing, a gateway allows the Power BI Service to refresh the dataset every morning.

Expected result after the refresh:

Metric Value
Total imported revenue 125,000

The gateway ensures that the service retrieves the current source values; it does not calculate business measures or store the imported data itself.

Best Practices and Common Pitfalls

  • Place the gateway close to the source: Use a stable server with reliable network connectivity to reduce latency and avoid routing traffic through user laptops.

  • Use clustering for availability: Multiple gateway instances can provide high availability. A cluster improves gateway availability, but it does not automatically distribute database workloads.

  • Prefer the standard gateway: Personal gateways are unsuitable for shared production refreshes because they are designed for individual use.

  • Secure the gateway host: Restrict administrative access, protect configuration files, and apply operating-system and application updates regularly.

  • Use least-privilege credentials: Grant only the read permissions needed by the dataset or query.

  • Schedule refreshes carefully: Avoid overlapping refreshes, stagger expensive queries, and prevent unrelated datasets from competing for the same gateway capacity.

  • Monitor performance: Review gateway logs and performance counters for slow sources, long-running queries, throttling, and connection failures.

  • Do not treat it as a general VPN: The gateway proxies specific data-source requests; it is not a replacement for broader private-network connectivity.

47

How do you schedule a Power BI semantic model refresh?

A scheduled refresh automatically refreshes a published Power BI import semantic model in Power BI Service. Configure the schedule after publishing the model; the Refresh button in Power BI Desktop updates only the local model and cannot create a Service schedule.

Read full answer

Applies toPower BI Desktop (current release) and Power BI Service (current release)

How Scheduling Works

Storage mode Scheduled model refresh Behaviour
Import Yes Power BI loads source data into the model on the configured schedule.
DirectQuery No Reports query the source at interaction time, so imported data does not need refreshing.
Live Connection No Data freshness is controlled by the connected Analysis Services source.

For an import model, the Service executes the model’s Power Query queries, reloads the relevant data, and makes the refreshed results available to reports.

Configure a Scheduled Refresh

  1. Publish the semantic model

  2. Build the model in Power BI Desktop, preferably using a star schema.

  3. Select Publish and choose the target workspace.

  4. Open that workspace in Power BI Service and confirm that the semantic model is present.

  5. Prepare capacity and connectivity

  6. Ensure the workspace has an appropriate licence or capacity, such as Power BI Pro, Premium, or Fabric.

  7. For private on-premises sources, install and start Power BI gateway on a machine that can reach the source, then add the source to the gateway.

  8. Cloud sources such as Azure SQL Database generally do not require a gateway when they are reachable from Power BI Service.

  9. The gateway must be available whenever the scheduled refresh is expected to run.

  10. Configure stored credentials

  11. In Power BI Service, open the semantic model’s More options (...) > Settings.

  12. Under Data refresh credentials, select the appropriate authentication method and store the required source credentials.

  13. For supported sources, use a dedicated least-privilege service principal for unattended refreshes.

  14. For gateway-backed sources, the gateway administrator must add the service principal to the gateway data source credentials and, where applicable, map it as the effective identity.

  15. Create the schedule

  16. In the semantic model settings, open Schedule refresh.

  17. Enable or define the schedule, select its frequency, days, and time, and choose the appropriate time zone.

  18. Select Apply.

  19. Test and monitor

  20. Use Refresh now from the semantic model’s options to test the configuration.

  21. Review Refresh history to identify the failed stage when a refresh fails.

  22. Enable failure notifications under Data refresh notifications so the owner receives an email.

  23. Refreshes can be delayed when the capacity is busy, and supported concurrency depends on the workspace’s capacity.

Incremental Refresh

For a large import model, configure an incremental refresh policy for the relevant fact table in Power BI Desktop’s Power Query editor, not in the Service settings.

  • Select the table in the Power Query editor and open its Incremental refresh configuration.

  • Choose a Date or DateTime column that the source can filter efficiently.

  • Configure the refresh and retention windows and verify that the generated filters fold into the source query.

  • Partition large sources where possible and remove unnecessary columns.

  • Publish the updated semantic model, then configure its scheduled refresh in Power BI Service; incremental refresh does not replace the Service schedule.

Business Example

A daily sales source contains:

Date Amount
2025-01-01 100
2025-01-02 120
2025-01-03 140
2025-01-04 110

If the upstream process completes the January 4 load at 05:30 and the refresh is scheduled for 06:00 in the correct time zone, the result is:

Metric Expected result
Rows loaded 4
Total amount 470
Latest sales date 2025-01-04

A clock-based schedule does not wait for an upstream process, so the 06:00 refresh must be later than the source’s completion-time guarantee.

Code Example

A measure can validate the total for the stated sample period:

Sales Amount (Sample Check) =
VAR StartDate = DATE(2025, 1, 1)
VAR EndDateExclusive = DATE(2025, 1, 5)
VAR VisibleSales =
    FILTER(
        Sales,
        Sales[Date] >= StartDate
            && Sales[Date] < EndDateExclusive
    )
RETURN
    SUM(VisibleSales[Amount])

For the sample data, Sales Amount (Sample Check) returns 470. This measure validates imported data; it does not create the refresh schedule.

Best Practices and Troubleshooting

  • Use a star schema: Model facts separately from dimensions and use one-to-many relationships from dimensions to facts. Avoid bidirectional filtering unless it is necessary.

  • Optimize source queries: Remove unused columns, filter early, preserve query folding, and partition large sources.

  • Operate the gateway centrally: Use a managed, monitored gateway rather than depending on an unattended personal workstation.

  • Protect source systems: Use read-only refresh identities and avoid unnecessarily weakening Privacy Levels settings.

  • Coordinate refresh timing: Schedule only after upstream ETL is expected to finish, and stagger large models to reduce capacity contention.

  • Monitor failures: Common causes include expired credentials, stopped gateways, unavailable sources, query timeouts, and capacity queues.

  • Republish model changes: Changes made in Power Query or the data model do not affect the Service until the semantic model is republished.

48

What is incremental refresh in Power BI?

Incremental refresh is a Power BI dataset-refresh strategy that retains historical data and reloads a configured recent date range during service refreshes. It reduces refresh duration, gateway load, and source-system consumption compared with reloading the entire dataset.

Read full answer

Applies toPower BI Desktop and Power BI Service (current release)

How It Works

Power BI applies an incremental-refresh policy to a table with a reliable Date or Date/Time column.

  • Refresh period: Rows within this range are eligible to be reloaded on every service refresh.

  • Archive period: Older rows remain available in the model for reporting but are not reloaded under the unchanged policy.

  • Source filtering: The Power Query query must be able to retrieve both the archived and refresh ranges, with filtering pushed down to the source where possible.

  • No automatic change detection: Incremental refresh does not compare partitions or identify changed rows. It reloads the configured refresh range even when some rows have not changed.

  • Historical data remains reportable: Archiving controls refresh behavior; it does not automatically hide old dates from measures or visuals.

Configuration and Business Example

  1. Import the fact table in Power Query and verify that the incremental-refresh column has a consistent Date or Date/Time type without null or invalid values.

  2. In Power BI Desktop, select Modeling > Incremental refresh, choose the column, and configure the archive boundary and refresh start.

  3. Verify that the generated source filter covers the required archive and refresh ranges and folds to the source.

  4. Publish the dataset to a workspace backed by Power BI Premium, Power BI Premium Per User, or a Microsoft Fabric capacity.

  5. Configure scheduled or on-demand refresh in Power BI Service. An on-premises source requires an appropriately configured on-premises data gateway.

  6. Monitor the first refresh and validate both refreshed and archived values before relying on the policy in production.

Suppose Fact Sales contains:

OrderDate Amount
2024-01-10 100
2024-02-10 150
2024-03-05 200
2024-04-02 250

The policy archives data before 2024-03-01 and refreshes data from 2024-03-01 onward. If the March source value changes from 200 to 220, the next refresh produces:

OrderDate Reported Amount Refresh Behavior
2024-01-10 100 Archived; not reloaded
2024-02-10 150 Archived; not reloaded
2024-03-05 220 Reloaded from source
2024-04-02 250 Reloaded from source

If the January source value changes to 110, the report continues to show 100 under the unchanged policy. It becomes eligible for correction only after the policy is changed so January falls within the refreshed range.

Modeling and Refresh Behavior

  • A separate Date table should contain unique dates, be marked as a date table, and relate to the fact table with one-to-many cardinality.

  • Fact-table rows may legitimately share the same date; duplicate dates are not a reason to create a distinct Date dimension row for each fact.

  • Relationships should normally flow from dimensions to facts. Bidirectional filtering should be used only when a documented requirement cannot be met with a standard relationship.

  • Measures are evaluated when a report is queried and normally remain preferred over calculated columns for business calculations.

  • Calculated columns are materialized during data processing and can increase refresh time and memory consumption.

  • Measures can use archived and refreshed rows together; incremental refresh does not change filter-context semantics.

  • Scheduled refresh, on-demand refresh, and gateway refresh all use the dataset’s incremental-refresh policy.

  • The service refresh schedule is separate from the configured archive and refresh date ranges.

Best Practices and Common Pitfalls

  • Keep the refresh window large enough to capture late-arriving data and source corrections.

  • Use query folding and ensure the source can filter efficiently on the incremental-refresh date column.

  • Keep the date column reliable, consistently typed, and free of invalid or null values.

  • Monitor refresh duration, processed-row counts, capacity memory usage, gateway logs, and refresh history.

  • Do not expect incremental refresh to detect which individual rows or partitions changed.

  • Retain only the historical range required by report and audit requirements because archived rows still consume model storage and memory.

  • Test the policy with representative late-arriving and corrected data before enabling a production schedule.

49

What are Power BI dataflows and how do they work?

A Power BI dataflow is a reusable, workspace-level data-preparation object that uses Power Query to connect, transform, and standardize source data for Power BI semantic models. An internal dataflow stores prepared data in Power BI, while an external dataflow evaluates a supported query against an external source without materializing the output in Power BI.

Read full answer

Applies toPower BI Service (current release) and Power BI Desktop (current release), using Power Query M

Dataflow Components

  • Entities: Each output table produced by a dataflow is called an entity.

  • Power Query transformations: Queries clean, combine, reshape, and standardize source data.

  • Source connections: Dataflows connect to supported cloud, SaaS, and on-premises sources.

  • Reusable preparation: Multiple semantic models can consume the same curated entities.

  • Separate calculation layer: Dataflows contain data preparation, but not report visuals, semantic-model relationships, or DAX measures.

Aspect Internal dataflow External dataflow
Prepared output Materialized in Power BI Remains in the source system
Main benefit Reusable, managed copy of curated data Avoids copying data into Power BI
Constraint Source must support the required connections and transformations Restricted to supported sources and transformations that can be evaluated externally

Creating and Consuming a Dataflow

  1. In Power BI Service, open the target workspace.

  2. Select New > Dataflow > Blank dataflow.

  3. Choose a source and authenticate through the connection window.

  4. Open Transform data to build the Power Query logic.

  5. Name each output entity, select Close & Apply, and save the dataflow.

  6. In Power BI Desktop, select Home > Get Data > Power BI dataflows, choose the workspace and dataflow, select the required entities, and choose Load. Power BI Desktop creates a DirectQuery model over the dataflow rather than importing its output as a normal table.

  7. In Power BI Service, start a report or create a semantic model from the dataflow and connect it to the required entities.

Business Example

Suppose a sales file contains the following records. Revenue is derived by multiplying Quantity by UnitPrice.

OrderID OrderDate Region Product Quantity UnitPrice Cost Revenue
101 2025-01-05 East Widget 2 50 30 100
102 2025-01-06 East Gadget 1 120 70 120
103 2025-01-07 West Widget 3 40 25 120
104 2025-01-08 West Gadget 2 100 55 200
105 2025-01-09 East Widget 1 55 32 55

The expected total revenue is 595. Revenue for the East region is 275, and revenue for the West region is 320. These figures can be displayed with measures and report filters in a consuming semantic model.

Refresh, Dependencies, and Security

  • Scheduled refresh: Open the dataflow’s workspace, select Settings > Refresh, and configure the refresh schedule and relevant time-zone settings.

  • Entity refresh: A standard dataflow refresh processes all entities in that dataflow; entities cannot be refreshed independently.

  • Gateway: On-premises sources require an appropriately configured on-premises data gateway accessible to the tenant that owns the workspace.

  • Downstream models: A consuming semantic model uses the dataflow’s available output. Schedule the dataflow refresh before dependent semantic-model refreshes and avoid overlapping windows.

  • Lineage: Workspace lineage views show dependencies and help identify the impact of failed upstream refreshes.

  • Security: Implement row-level security in the consuming semantic model; a dataflow is not an end-user RLS enforcement layer.

Best Practices and Limitations

  • Centralize reusable logic: Use a dataflow when several semantic models need the same curated, business-ready data.

  • Filter early: Remove unnecessary rows and columns before joins or appends to reduce source and processing cost.

  • Preserve query folding: Apply supported filtering, projection, and aggregation as early as possible.

  • Use parameters: Keep environment-specific URLs, folder paths, and credentials outside repeated query logic.

  • Use incremental refresh selectively: For large append-only fact tables in an internal dataflow, configure incremental refresh when the source provides the required date and time information.

  • Model downstream: Build a star schema, relationships, measures, and business calculations in the semantic model rather than embedding report logic in the dataflow.

  • Choose the correct architecture: Use an internal dataflow for reusable imported data, an external dataflow for supported source-side evaluation, and source-native DirectQuery when near-real-time access is required. DirectQuery over a standard dataflow still depends on that dataflow’s refresh schedule.

50

How do deployment pipelines promote Power BI content between environments?

Deployment pipelines in Power BI Service promote semantic models, reports, and dashboards through controlled environment stages. Each promotion is an explicit action, and source-system data is refreshed independently in the target workspace.

Read full answer

Applies toMicrosoft Power BI Service and Power BI Desktop (current release)

How They Work

A deployment pipeline contains two or three stages, normally mapped to development, test, and production environments:

Stage Typical Purpose Example Workspace
Development Create and modify models and reports Sales-Dev
Test Validate changes with testers and stakeholders Sales-Test
Production Publish approved content to consumers Sales-Prod
  • Each stage is associated with a workspace in a supported Power BI Premium or Microsoft Fabric capacity.

  • A deployment copies selected Power BI content and metadata from a source stage to a target stage.

  • Selected source items overwrite their corresponding target items.

  • An all-content deployment can also remove target content that does not exist in the source stage, so it must be used carefully.

  • Deployment pipelines do not promote Power BI apps directly. Deploy the app’s content first, then publish or update the app separately.

Configure and Promote Content

  1. Create the workspaces

  2. Create Sales-Dev, Sales-Test, and Sales-Prod in Power BI Service.

  3. In each workspace’s Settings > Capacity, assign the required Premium or Fabric capacity.

  4. Create the pipeline

  5. Open Deployment pipelines in the Power BI Service navigation.

  6. Select Create pipeline, name it Sales Pipeline, and add the stages.

  7. Associate each stage with its corresponding workspace.

  8. Publish development content

  9. In Power BI Desktop, select File > Publish to Power BI.

  10. Publish the semantic model and report to Sales-Dev.

  11. Deploy between stages

  12. Open the pipeline and select Deploy content.

  13. On the Compare and apply page, review model, report, and dashboard differences.

  14. Select the items to deploy and confirm the operation.

  15. Repeat from Test to Production after validation.

  16. Validate production

  17. Check gateways, data-source credentials, parameter values, refresh schedules, and workspace permissions.

  18. Refresh the production semantic model and verify key measures, filters, visuals, and security behavior.

Business Example

Suppose the source system contains:

Region Sales Amount
East 120,000
West 95,000
Central 85,000
Total 300,000

A regional sales report and total-sales KPI are published to Development and promoted through Test to Production. After the production semantic model is refreshed successfully, the KPI displays 300,000. The pipeline did not copy these source rows; it promoted the model and report definitions.

Code Example

The following PowerShell example invokes the Deploy Pipeline REST API. It requires Azure CLI authentication and sufficient Power BI workspace permissions. It performs an explicit deployment from one stage to another.

# Run `az login` first. The account needs access to the pipeline and workspaces.
$token = (
    az account get-access-token `
        --resource https://analysis.windows.net/powerbi/api |
    ConvertFrom-Json
).token

$pipelineId = Read-Host "Pipeline ID"
$sourceStageId = [int](Read-Host "Source stage ID, for example 0")
$targetStageId = [int](Read-Host "Target stage ID, for example 1")
$sourceWorkspaceId = Read-Host "Source workspace GUID"
$targetWorkspaceId = Read-Host "Target workspace GUID"

$payload = @{
    sourceStageId     = $sourceStageId
    targetStageId     = $targetStageId
    sourceWorkspaceId = $sourceWorkspaceId
    targetWorkspaceId = $targetWorkspaceId
} | ConvertTo-Json

Invoke-RestMethod `
    -Method Post `
    -Uri "https://api.powerbi.com/v1.0/myorg/pipelines/$pipelineId/deploy" `
    -Headers @{ Authorization = "Bearer $token" } `
    -ContentType "application/json" `
    -Body $payload

Use Compare and apply in the service interface when item-level selection or a detailed comparison is required.

Important Boundaries

  • Source data is not deployed: The target semantic model obtains rows through its own refresh.

  • Credentials are environment-specific: Configure gateway credentials separately for each environment.

  • Parameter values need review: Parameter definitions and values can be affected by deployment, so confirm that the target contains the intended values.

  • Refresh is separate: Deployment does not automatically refresh the target model.

  • Gateway configuration is separate: Verify that the target workspace can reach the intended data sources.

  • RLS definitions are promoted, but access is not: Review role definitions in the target and separately assign users, groups, and workspace permissions.

  • It is not source control: Deployment pipelines provide controlled promotion, not Git branches, pull requests, or commit history.

Best Practices

  • Use a star schema with conformed dimensions and a central fact table to keep relationships predictable and queries efficient.

  • Keep business calculations in measures and validate them in Test before production promotion.

  • Maintain separate environment-specific credentials and parameter values.

  • Require peer review and stakeholder sign-off before every production deployment.

  • Compare model changes carefully to avoid overwriting report modifications or removing target content.

  • Use PBIP or TMDL source control where supported; it complements deployment pipelines but does not replace release promotion.

51

What is Microsoft Fabric and how does Power BI fit in?

Microsoft Fabric is a cloud data and analytics platform that combines data integration, engineering, storage, governance, real-time processing, and business intelligence within the Microsoft ecosystem. Power BI is Fabric’s reporting and semantic-modelling layer, providing interactive reports, dashboards, DAX calculations, and governed consumption of data prepared through Fabric.

Read full answer

Applies toMicrosoft Fabric, Power BI Desktop, and Power BI Service (current releases)

Microsoft Fabric Overview

Fabric organizes analytics around OneLake, a common data-lakehouse storage platform that can contain data from Microsoft and Azure services, other cloud providers, and on-premises systems.

Key Fabric capabilities include:

  • OneLake: Central storage for files, tables, and other data assets.

  • Data Factory: Data ingestion and workflow orchestration.

  • Data Engineering: Spark-based processing through Fabric Spark and notebooks.

  • Real-Time Intelligence: Streaming ingestion, event processing, and real-time analytics.

  • SQL analytics: Lakehouse SQL endpoints and dedicated Fabric warehouse capabilities.

  • Purview hub: Cataloguing, lineage, classification, and governance.

  • Power BI: Semantic models, measures, reports, dashboards, and report consumption.

Fabric workspaces bring these components together so that pipelines, lakehouse tables, warehouses, semantic models, reports, and apps can operate as governed data products.

How Power BI Fits into Fabric

Power BI remains a complete BI product rather than merely a visualization layer. Within Fabric, it mainly provides the semantic and presentation capabilities.

Concern Microsoft Fabric Power BI
Data storage OneLake, Lakehouse, and Warehouse Connects to source data; an Import model also has its own managed storage
Data preparation Data Factory pipelines, Spark, and notebooks Power Query/M in Power BI Desktop for local transformations
Semantic modelling Supports analytics data products Star schemas, relationships, calculated columns, and DAX measures
Consumption Data products and downstream workloads Reports, dashboards, mobile access, and embedded analytics
Governance Purview hub catalogue and lineage Semantic-model permissions, row-level security, and workspace roles
Operations Capacity, pipelines, and orchestration Semantic-model refresh, model management, and deployment pipelines

A typical flow is:

  1. Ingest source data into OneLake through a Data Factory pipeline.

  2. Clean and shape it in a Lakehouse or Warehouse.

  3. Connect Power BI Desktop to the Fabric Lakehouse, Warehouse, or SQL endpoint.

  4. Build a star-schema semantic model and DAX measures.

  5. Publish the report and semantic model to a Fabric workspace.

  6. Share the report directly or package it in a Fabric app.

In current Power BI Desktop, use Get Data > More options > Azure > Microsoft Fabric, then select the relevant workspace and Lakehouse. Available connections can include Lakehouse tables or the Lakehouse SQL endpoint. Depending on the connection, workload, and licensing, Power BI can use Import, DirectQuery, or Direct Lake against Fabric data.

Small Business Example

Suppose a retailer stores sales data in a Fabric Lakehouse table named Sales:

Product Revenue
Keyboard 50,000
Mouse 25,000
Monitor 75,000

A Fabric pipeline loads and validates the source orders, while Power BI connects to the Sales table and defines a Total Revenue measure that sums Sales[Revenue]. A clustered column chart grouped by Sales[Product] produces:

Product Displayed revenue
Keyboard 50,000
Mouse 25,000
Monitor 75,000

The Fabric pipeline provides repeatable data preparation, while Power BI turns the result into an interactive report that sales managers can filter by date, region, or product.

Best Practices and Common Pitfalls

  • Use Fabric for governed data workflows: Centralize recurring ingestion, validation, and transformation instead of duplicating them in every Power BI report.

  • Avoid unnecessary copies: OneLake shortcuts provide governed access to existing data without physically duplicating it.

  • Plan gateway connectivity: Use and monitor the appropriate gateway for on-premises sources and secure its credentials and network access.

  • Model for BI: Use a star schema with fact tables and conformed dimensions rather than exposing a wide operational table directly to every report.

  • Choose the connection deliberately: Import is effective for smaller models refreshed on a schedule; Direct Lake supports large OneLake-backed workloads and rapid changes; DirectQuery is appropriate when source-side querying is required.

  • Prefer measures for business logic: Keep reusable DAX calculations in measures and reserve calculated columns for row-level values that genuinely belong on each row.

  • Design clear reports: Use descriptive titles, visible units, meaningful defaults, and filters that support a specific user decision.

  • Secure and monitor the solution: Apply row-level security where users need different record access, and monitor refreshes, model performance, capacity usage, and Purview lineage.

52

How can you optimize Power BI report performance?

Performance optimization in Power BI means reducing unnecessary data, simplifying the model, writing efficient DAX, and limiting expensive visual work. The largest gains usually come from the source query and data model before individual measures or visuals.

Read full answer

Applies toMicrosoft Power BI Desktop and Power BI Service (current release); DAX Studio (current release)

Diagnose the Bottleneck

Use Performance Analyzer before changing the model:

  1. In Power BI Desktop, select Optimize > Performance Analyzer.

  2. Select Start recording, refresh, and interact with each page.

  3. Stop recording and inspect Visual, Query, and Other timings.

Compare candidate visuals in separate recording runs to isolate rendering problems. For deeper analysis, connect DAX Studio to Power BI Desktop and inspect server timings, query plans, and measure evaluation.

Optimize Data Import and Power Query

  • Remove unnecessary columns and rows early: Use Home > Manage Columns > Remove Columns or Remove Other Columns. Unused text, binary, and high-cardinality fields increase model size.

  • Preserve query folding: Apply filtering, projection, and aggregation close to the source. Use View Native Query, where available, to confirm that transformations are folded.

  • Avoid transformations that block folding: Custom M logic and row-by-row table generation may require the source to return a large dataset.

  • Choose the appropriate storage mode: Import usually gives the fastest interactive performance. DirectQuery reduces local storage but depends on source latency, capacity, and concurrent query load.

  • Remove duplicate or unused queries without breaking downstream dependencies.

  • Schedule refreshes to match actual data-update requirements rather than refreshing more frequently than necessary.

Design an Efficient Data Model

Prefer a star schema:

  • Fact tables contain measures, dates, and foreign keys.

  • Dimension tables contain customer, product, geography, and calendar attributes.

  • Use one dimension row to many fact rows, with single-direction filtering from each dimension to its fact table.

  • Avoid unnecessary bidirectional relationships because they can introduce ambiguous paths and increase query complexity.

  • Test dimension keys for missing or invalid values because Power BI relationships do not enforce referential integrity.

Create a Date table with unique, continuous dates covering all fact dates. Select Table tools > Mark as date table, then relate Date[Date] as the one side to the fact date on the many side. This supports reliable time-intelligence functions.

Optimize DAX and Report Design

  • Use measures for aggregate logic evaluated in report filter context. Use calculated columns only when row-level values should be stored during refresh, because they increase model size and processing time.

  • Use VAR and RETURN to evaluate intermediate results once and improve readability.

  • Limit iterator functions such as SUMX and CONCATENATEX over large tables; prefer direct aggregation and simple predicate filtering. FILTER is a table function rather than an iterator.

  • Avoid repeating expensive distinct-count or string calculations inside CALCULATE. A measure evaluates in filter context, while iterator functions introduce row context; CALCULATE can transition that row context into filter context.

  • Use USERELATIONSHIP for a required inactive relationship rather than making every relationship bidirectional.

  • Keep pages focused, limit slicers and interactions, and avoid high-cardinality slicers, very large scatter plots, and custom visuals that process large datasets in the browser.

  • Use meaningful labels and consistent colors to keep the report easy to interpret without adding unnecessary visual complexity.

Code Example

Use this sample sales fact table:

OrderID OrderDate Amount
O-100 2025-01-05 100
O-101 2025-01-08 50
O-102 2025-01-20 200
O-103 2025-02-03 150

Relate a continuous Date table to Sales[OrderDate], with Date[Date] as the one side and single-direction filtering, then create:

Average Order Value :=
VAR
    TotalRevenue = SUM ( 'Sales'[Amount] )
    OrderCount = DISTINCTCOUNT ( 'Sales'[OrderID] )
RETURN
    DIVIDE ( TotalRevenue, OrderCount )

For January 2025, the expected revenue, order count, and average order value are 350, 3, and 116.67. For February, they are 150, 1, and 150.00. Across all four orders, they are 500, 4, and 125.00.

Best Practices and Common Pitfalls

  • Optimize from measurements rather than assumptions; test with production-scale data, representative filters, and real security roles.

  • Profile memory usage and query timing after every major model change.

  • Use incremental refresh when the source, refresh policy, and licensing support it. For very large semantic models, assess partitioning separately through supported XMLA workflows on suitable capacities.

  • In Power BI Service, use gateways for private sources, monitor refresh failures and duration, and size capacity for concurrency and dataset size.

  • Publish tested content through workspaces and apps. Separate development and production with deployment pipelines, source control, and parameterized connections rather than hard-coded development sources.

  • Avoid direct edits to production reports or datasets that should be managed through deployment pipelines or another controlled release process.

53

What is Performance Analyzer in Power BI Desktop?

Performance Analyzer is a Power BI Desktop diagnostic pane that shows how long report visuals take to refresh and helps identify whether the delay comes from DAX queries, formula processing, or visual rendering. It is a primary tool for investigating slow reports because it provides visual-level and query-level timings rather than requiring the analyst to guess at the cause.

Read full answer

Applies toMicrosoft Power BI Desktop (current release)

How It Works

Performance Analyzer records refresh activity for the visuals in a report. When you select a visual, it separates the visual’s refresh time into several components:

  • DAX queries: Time spent executing the queries generated for the visual.

  • Visual display: Time spent preparing and rendering the visual.

  • Other: Additional processing not included in the DAX query or display timing.

  • Total duration: The overall refresh duration for the selected visual.

  • Refresh duration: The time reported for the visual during the report refresh.

A single visual may generate multiple DAX queries. Query details can expose the generated DAX and show storage-engine (SE) and formula-engine (FE) query counts and durations.

These timings are diagnostic rather than a complete measurement of end-user performance. They do not directly represent browser rendering, network latency, Power BI Service capacity contention, or the full time required to open a published report.

How to Use Performance Analyzer

  1. Open the report in Power BI Desktop.

  2. Select View > Performance analyzer.

  3. Optionally select Clear cache when you need to test processing without cached results.

  4. Select Refresh visuals in the Performance Analyzer pane to capture refresh timings.

  5. Select a visual in the report or in the Performance Analyzer visual list.

  6. Review its refresh duration and expand the individual DAX queries.

  7. Inspect the generated query and investigate relevant measures, relationships, columns, and model design.

  8. Make one model or measure change at a time, refresh again, and compare the results.

The visual list helps identify slow visuals, while the query details help explain why a particular visual is expensive. Refresh timings can vary between runs, so compare repeated measurements rather than relying on one result.

Example: Diagnosing a Slow Sales Visual

Suppose the Sales table contains product transactions and the report contains a matrix showing Product and Net Sales, where the measure is SUM(Sales[Amount]) - SUM(Sales[Discount Amount]).

Sample data:

Product Amount Discount Amount
Product A 100 10
Product B 200 20
Product C 150 15

Expected result:

Product Net Sales
Product A 90
Product B 180
Product C 135

In Performance Analyzer, selecting the matrix reveals the DAX query behind the visual. If query time is high, the analyst should investigate unnecessary calculated columns, complex relationship paths, large fact tables, or expensive measures. If DAX query time is low but visual display time is high, the problem is more likely related to the visual, such as excessive detail, too many formatting rules, or high-cardinality fields.

Interpretation and Best Practices

  • Start with the model: Use a star schema with a fact table and conformed dimension tables.

  • Prefer measures for aggregations: Measures calculate dynamically and generally perform better than calculated columns for values that must aggregate across many rows.

  • Reduce unnecessary DAX work: Use VAR and RETURN to reuse results, remove redundant calculations, and avoid deeply nested IF logic.

  • Avoid high-cardinality calculated columns: A calculated column is evaluated for every row and can increase model size and processing time.

  • Keep visuals focused: Limit visual elements, remove unnecessary drill-down levels, and exclude fields that do not support the business question.

  • Test with realistic data: Analyze a dataset with representative row counts and relationships because a small model may hide performance problems.

  • Compare before and after: Record a baseline, apply one targeted change, and measure again.

  • Remember the environment: Power BI Desktop timings describe local processing. For published reports, also review Power BI Service capacity metrics, refresh history, and usage information through the appropriate admin and monitoring tools.

Common Pitfalls

  • Treating the sum of individual visual timings as total page-load time; visuals may refresh in parallel.

  • Assuming a long visual duration is caused by DAX when rendering is the larger component.

  • Optimizing a measure without checking the underlying model and relationships.

  • Comparing runs that used different cache states; record comparable cold and warm runs separately.

  • Treating Power BI Desktop results as a guarantee of Service performance because Service runs under different capacity and concurrency conditions.

54

What is DAX Studio used for in Power BI?

DAX Studio is an external diagnostic tool for testing DAX queries, inspecting measure evaluation, and analyzing the performance of calculations in Power BI semantic models. It provides more detailed server timings and query-plan information than Power BI Desktop’s Performance Analyzer, but it does not automatically optimize the model.

Read full answer

Applies toDAX Studio 3.x with Power BI Desktop (current release)

Core Capabilities

  • Query execution: Run DAX queries against a local Power BI Desktop model or a compatible Analysis Services instance and inspect the returned table.

  • Performance analysis: Measure how much work is performed by the formula engine, storage engine, data scans, cache operations, and other query-plan events.

  • Measure debugging: Test measures with controlled filters and compare the behaviour and performance of different DAX expressions.

  • Context testing: Use filters to examine how row context and filter context affect a calculation.

  • Query formatting: Format DAX queries for easier reading before sharing them with developers.

  • Metadata exploration: Inspect model objects and evaluate expressions without changing report visuals.

DAX Studio helps diagnose a model, but the analyst must interpret the results and make any necessary changes to measures, relationships, data types, or model design.

Connect to Power BI Desktop

  1. Open the .pbix file in Power BI Desktop and load or refresh the data as needed so its local Analysis Services instance is running.

  2. Start DAX Studio.

  3. Select the local Power BI Desktop instance from the server list.

  4. Choose Connect.

  5. Enter a DAX expression in the query editor.

  6. Press F5 to execute it.

  7. Review the returned rows and execution information in the Server Timings and Query Plan panes.

For deployed models, DAX Studio can connect to supported Analysis Services instances and, where XMLA connectivity is enabled and authorized, Power BI XMLA endpoints. Access to a Power BI workspace does not by itself guarantee XMLA connectivity.

Interpreting Performance Output

The Server Timings and Query Plan views expose the work performed by a query:

  • Formula Engine (FE): Evaluates calculation logic and query operations that cannot be handled directly by the storage engine.

  • Storage Engine (SE): Retrieves data and performs supported aggregations.

  • Scan events: Show which tables and columns are being read. Large scans can indicate inefficient filters or an unsuitable model.

  • Cache events: Record cache lookups and cache stores, helping explain changes in execution behaviour.

  • Total duration: Shows the overall elapsed execution time represented by the query plan.

Compare several equivalent expressions over repeated runs rather than trusting one result. Cache state, server activity, and other environmental factors can affect timings.

Business Example

Suppose the Sales table contains:

Product Category Quantity Unit Price
Keyboard Accessories 2 50
Monitor Displays 1 400
Mouse Accessories 3 20

A measure named Sales Amount uses the valid measure-definition syntax Sales Amount = SUMX('Sales', 'Sales'[Quantity] * 'Sales'[Unit Price]). It sums the revenue generated by each sales row.

Evaluating the measure for each category produces:

Category Sales Amount
Accessories 160
Displays 400

The category grouping establishes a filter context for each category. Within SUMX, each row of Sales receives a row context, allowing Quantity and Unit Price to be evaluated for that row before the results are aggregated.

Best Practices and Limitations

  • Test representative filters: Performance without filters may differ substantially from performance with realistic business filters.

  • Use variables for readability: VAR and RETURN often make logic explicit and prevent unnecessary expression duplication.

  • Optimize the model first: Measures work best with a star schema, correctly typed columns, and relationships with appropriate cardinality and filter direction.

  • Test iterator alternatives carefully: SUMX and similar functions iterate row by row. Compare them with semantically equivalent measures or precomputed columns where appropriate, while considering the additional refresh and storage costs of calculated columns.

  • Watch calculated columns: Calculated columns are evaluated for every row during refresh or recalculation, whereas measures are evaluated when queried.

  • Do not judge only total duration: Review the query plan and scan volume as well as elapsed time.

  • Use cautiously against production models: DAX queries consume model resources and should be restricted to trained users and controlled environments.

55

How can you reduce the size of a Power BI data model?

Reducing the size of a Power BI data model means loading fewer rows and columns, using efficient data types, removing unnecessary calculated columns and tables, and selecting an appropriate storage mode. The largest gains usually come from filtering, aggregating, or archiving data before it remains in the model.

Read full answer

Applies toPower BI Desktop (current release) and Microsoft Power BI Service (current release)

How It Works

A Power BI model’s size is mainly affected by:

  • Fact-table rows: Large transaction tables usually dominate imported storage.

  • Number of columns: Every imported column consumes storage, even when rarely used.

  • Data types and cardinality: Column data types affect VertiPaq compression; high-cardinality text generally consumes more space than integer keys or dates.

  • Calculated columns and tables: These values are materialized and stored with the model.

  • Storage mode: Import stores source data locally, while tables in DirectQuery mode have their data queried at the source.

Relationships primarily affect query performance rather than model size. Unnecessary bidirectional or many-to-many relationships can make queries slower and harder to optimize, so they should be removed when the business requirement permits.

Example Business Model

Suppose the source contains transaction-level sales:

Date ProductKey Amount
2024-01-01 101 10
2024-01-01 101 5
2024-01-02 101 7

If reports require only daily product totals, aggregate before importing:

Date ProductKey Amount
2024-01-01 101 15
2024-01-02 101 7

This reduces three source rows to two model rows while preserving the required daily-product grain. In a large dataset, the same transformation can remove millions of rows, provided that lower-level detail is not required.

Code Example

In Power Query, create a new query based on the existing Sales query and group the data by date and product:

let
    Source = Sales,
    Grouped = Table.Group(
        Source,
        {"Date", "ProductKey"},
        {
            {"Amount", each List.Sum([Amount]), type number}
        }
    ),
    Typed = Table.TransformColumnTypes(
        Grouped,
        {
            {"Date", type date},
            {"ProductKey", Int64.Type},
            {"Amount", type number}
        }
    )
in
    Typed

In Power BI Desktop:

  1. Select Home > Transform data to open Power Query Editor.

  2. Select the Sales query and create the summarized query above.

  3. In the Queries pane, right-click unused queries and select Enable Load to disable their loading.

  4. Select Close & Apply.

  5. In Data view, verify the imported column data types.

  6. In Model view, create relationships using unique dimension keys.

Prefer a measure instead of a calculated column when the value can be calculated from existing columns:

Total Sales :=
VAR TotalAmount =
    SUM ( Sales[Amount] )
RETURN
    TotalAmount

For the sample data, the measure returns 22. A measure is evaluated when needed, whereas a calculated column is calculated and stored for every row, increasing model storage and refresh time.

Storage Modes and Refresh

  • Import: The usual choice for most models. It stores compressed data locally and provides fast interactive performance.

  • DirectQuery: Sends queries to the source rather than importing the table’s data. It reduces local data storage but requires a capable source, suitable indexes and predictable query performance.

  • Composite model: Imports selected tables while querying others directly. Use it when some data requires local performance and other data must remain current.

For large fact tables:

  • Filter source data to the required business period and columns.

  • Configure incremental refresh and, when historical data must be removed, an archive period in the table’s incremental-refresh policy in Power BI Desktop. Publish the model and use Power BI Service to schedule and monitor refreshes.

  • Incremental refresh by itself reduces refresh workload; the archive policy is what allows old rows to be removed from the model.

  • Use an on-premises data gateway for private on-premises sources.

  • Preserve query folding whenever the source supports it. Source-side filtering and aggregation generally reduce refresh time and imported data volume.

Best Practices and Pitfalls

  • Remove unused columns early: In Power Query, select the columns and choose Remove Columns.

  • Avoid loading exploratory tables: Keep them in the source system until their business purpose is confirmed.

  • Use compact data types: Store keys as whole numbers where appropriate, dates as date or datetime values, and avoid numeric values stored as text.

  • Model a star schema: Keep facts at a consistent grain and use dimension tables for descriptive attributes.

  • Use single-direction relationships where possible: Avoid bidirectional filtering unless the business requirement clearly justifies it.

  • Keep calculated columns limited: Use them only when the logic must be evaluated for every row.

  • Do not rely on RLS to reduce model size: Row-level security controls which rows users can see; it does not remove rows from the underlying imported model.

  • Test after each change: Compare row counts, model size, refresh duration and report performance, and confirm that aggregation has not removed detail required by the business.

56

What is the VertiPaq engine in Power BI?

VertiPaq is Power BI’s in-memory columnar storage and query-processing engine. It compresses imported data, stores it in an optimized analytical model, and evaluates DAX measures efficiently for reports and dashboards.

Read full answer

Applies toMicrosoft Power BI Desktop (current release) and Power BI Service (current release)

Core Architecture

VertiPaq is the engine behind a Power BI Import model. Power Query retrieves and transforms source data, while VertiPaq stores the transformed data and model metadata in memory.

It provides:

  • Columnar storage: VertiPaq reads and processes values by column rather than by row, which suits analytical queries that aggregate or filter many records.

  • Compression: Repeated values are encoded efficiently. Techniques such as dictionary encoding make low-cardinality columns, including status, category and country columns, inexpensive to store.

  • Optimized model storage: Fact tables, dimension tables, relationships, hierarchies, calculated columns and calculated tables are maintained as part of the model.

  • DAX query execution: Measures are evaluated at query time against the stored data using the active filter context.

There is no separate VertiPaq configuration to enable. Loading data through Home > Get Data and choosing Import in the connector’s data connectivity mode—or accepting the default Import behaviour where no separate choice appears—uses it automatically.

How Data Flows Through VertiPaq

  1. Power BI connects to a source such as Excel, SQL Server, SharePoint or an API.

  2. Power Query performs cleaning and transformation operations.

  3. Close & Apply loads the transformed data into the VertiPaq model.

  4. Relationships, calculated columns and calculated tables are processed or materialized.

  5. Report visuals send filter and aggregation requests to VertiPaq.

  6. VertiPaq evaluates measures and returns the requested results.

Measures remain lightweight definitions, whereas calculated columns are calculated for each source row and materialized during refresh. Large calculated tables can therefore increase refresh time and memory consumption.

Business Example

Suppose Sales contains:

DateKey ProductKey Amount
20250105 1 100
20250106 1 150
20250107 2 200

The related Products dimension contains:

ProductKey Product
1 Widget
2 Gadget

A measure defined as Total Sales = SUM(Sales[Amount]) returns 450.

A measure such as Widget Sales = CALCULATE([Total Sales], Products[Product] = "Widget") applies the product filter through CALCULATE and returns 250.

VertiPaq can efficiently scan the numeric Amount column, compress repeated dimension values and evaluate the filter context without querying the source database for every visual.

Import Versus DirectQuery

Characteristic Import with VertiPaq DirectQuery
Data location Primarily in the memory-optimized VertiPaq model Primarily in the source database
Query performance Fast for aggregations and common dashboard queries Depends on source latency and network performance
Data freshness Refresh-based Source-dependent; it avoids scheduled Import refreshes, but source, network and any caching affect latency
Memory usage Consumes capacity memory Dataset metadata still consumes resources
Typical use case Executive dashboards and large-scale analysis Large or frequently changing data that should remain in source

DirectQuery does not use VertiPaq as the primary storage for source fact data, although the model, relationships and DAX evaluation still exist.

Advantages and Limitations

  • Advantages: Fast aggregations, effective compression, sophisticated relationship handling, responsive interactive reports and a reusable semantic model.

  • Limitations: Data is refresh-bound in Import mode, the model must satisfy capacity and dataset-size limits, and poorly modeled high-cardinality columns can slow refresh and queries.

  • Capacity impact: Large datasets, frequent refreshes and many concurrent users can increase memory pressure and affect Power BI Service capacity performance.

Best Practices

  • Use a star schema: Keep transactional facts in fact tables and descriptive attributes in dimension tables. Store measures in the semantic model, using dedicated measure tables when appropriate.

  • Import only required columns: Remove unused fields before loading data into VertiPaq.

  • Control cardinality: Avoid storing repeated descriptive text in fact tables; use dimension keys and related dimension tables instead.

  • Prefer measures: Use measures for aggregations and business logic. Use calculated columns only when row-level values are genuinely required.

  • Write readable DAX: Use VAR and RETURN to calculate intermediate results once, such as VAR TotalAmount = SUM(Sales[Amount]) RETURN TotalAmount.

  • Review relationships: Use single-direction relationships from dimension tables to fact tables unless bidirectional filtering is specifically required.

  • Create a dedicated date table: Mark it through Modeling > Mark as date table and use it for consistent date filters and time intelligence.

  • Disable automatic date/time only after adding a date table: In Options > Current File > Data Load > Time intelligence, disable the automatic date hierarchy.

  • Test performance: Use Power BI Desktop’s Performance Analyzer to identify slow visuals and measure-heavy DAX.

  • Plan refreshes carefully: For large fact tables, consider partitioning, incremental refresh and scheduled refresh windows rather than repeatedly loading every row.

57

Why should you avoid using too many calculated columns in Power BI?

Calculated columns in Power BI are evaluated row by row at data-refresh time and stored inside the model, so every extra one increases refresh duration and in-memory model size while locking the logic to that refresh cycle. Measures are the preferred choice for aggregation and business logic because they are evaluated at query time, respond instantly to slicers, and cost nothing until someone actually queries them. Too many calculated columns therefore trade a fast, flexible model for a slow, rigid one.

Read full answer

Applies toPower BI Desktop (current release, Import mode) and Microsoft Fabric Power BI Service

The Core Trade-Off

  • Calculated column: computed once per row during refresh, materialised in the model, and then behaves like a normal imported column. It is a fixed value until the next refresh.

  • Measure: an expression evaluated at query time, aggregated on demand. It reacts to slicers, cross-filters and the current filter context, and is recomputed whenever the visual is refreshed.

  • A calculated column can never reference a measure; a measure can reference measures.

  • Inside a calculated column, Sales[Amount] sits in row context (one row at a time); RELATED() can reach a dimension attribute in that row context. Measures work in filter context and are reshaped by CALCULATE.

Calculated Column vs Measure

Aspect Calculated column Measure
When evaluated At data refresh At query time
Storage Stored in the model, adds file size and memory Formula stored only, negligible size
Responds to slicers No (value is static per row) Yes
Business rule changes Needs a refresh Instant
Can reference measures No Yes
Typical use Categories, bands, surrogate keys, date helpers Totals, ratios, KPIs, time intelligence

Where the Cost Shows Up

  • Refresh time: each calculated column is a full pass over the table, evaluated row by row. Hundreds of columns with RELATED() lookups or heavy IF logic can turn a 2-minute refresh into a 20-minute one.

  • Model size: every calculated column adds a full column of values to the model, and text or high-cardinality values compress poorly, so wide tables of calculated columns inflate the in-memory model and can push you toward the 1 GB file-size limit for publishing to the Power BI Service.

  • Rigidity: a band like "High"/"Low" is baked into the data. If finance changes the threshold, the model must be refreshed and re-processed by downstream consumers before the change is visible.

  • Duplication of logic: if the same rule can be expressed as a measure, keeping the calculated column adds a second place to maintain the logic and a second source of truth.

When a Calculated Column Is the Right Choice

  • Derived grouping or banding that users want to slice on (a slicer needs a real column).

  • Surrogate keys for many-to-many relationships.

  • Date/time helper columns needed to build a relationship (year, month key, week).

  • Slowly-changing-dimension attributes that must be attached per row.

  • Static flags such as "IsWeekend" or "IsActive".

Use Power Query (M) for transformations such as cleansing, merging, renaming, unpivoting and type conversion — those belong upstream of the model and avoid the refresh-time cost of DAX entirely.

Example: Banding a Column with a Calculated Column

Suppose Sales contains an Amount column and a calculated column Sales[Amount Band] that assigns each row a band: amounts of 1,000 or more are "High", amounts from 500 to 999 are "Medium", and anything below 500 is "Low". The banding rule lives inside the column, so the threshold is fixed until the next refresh.

Order Date Product Amount Amount Band (calculated column)
2024-01-05 Widget A 1200 High
2024-01-11 Widget B 640 Medium
2024-02-02 Widget A 480 Low
2024-02-14 Widget C 1500 High

Expected results with no other filters applied:

Measure Result Why
Total Sales 3,820 SUM of Sales[Amount] over all four rows
High Band Sales 2,700 Sales[Amount] filtered to the "High" band (1,200 + 1,500)

The same total can be produced by a measure that filters Sales[Amount] directly, for example CALCULATE(SUM(Sales[Amount]), Sales[Amount] >= 1000), which also returns 2,700. The measure returns the same number but the threshold can be edited in the model without a refresh — at the cost of not being sliceable as a field, since a slicer needs a real column.

Best Practices and Common Pitfalls

  • Push work to Power Query: clean and reshape in M, aggregate in DAX, and only add a calculated column when the model genuinely needs a stored attribute.

  • Keep them few and purposeful: if a column merely duplicates a measure, delete it. Group calculated columns into one dedicated table (using RELATED()) so they are easy to review and remove.

  • Watch refresh cost: avoid RELATED() chains across several relationships, and avoid row-by-row logic that Power Query can do faster with a native M function.

  • Prune aggressively: disable load for unused source columns and delete unused calculated columns — unused columns still cost refresh time and memory.

  • Test performance: use Power BI Desktop’s Performance Analyzer and the refresh timing shown in the workspace refresh history to see which columns and queries dominate.

  • Remember the security angle: row-level security applies to measures and columns differently, so test any calculated column that carries access logic before rolling it out to a workspace.

58

How should many-to-many relationships be modeled in Power BI?

Handle a many-to-many relationship by introducing an association fact table at a clearly defined transaction or pairing grain whenever possible. This creates predictable one-to-many paths from dimensions to facts, whereas a direct many-to-many relationship is a narrower alternative for suitable Import-mode models.

Read full answer

Applies toMicrosoft Power BI Desktop (current release) and Power BI Service (current release)

Recommended Model

Suppose customers can purchase multiple products and each product can appear on many orders. Model Orders and Products as dimensions and use Order Lines as the associative fact table:

  • Orders[Order ID] → one-to-many → Order Lines[Order ID]

  • Products[Product ID] → one-to-many → Order Lines[Product ID]

Order Lines resolves the apparent many-to-many relationship between Orders and Products. Each row represents one line at the defined order-product grain. If an order can contain the same product on multiple transaction dates, retain transaction-level detail and add appropriate transaction or date dimensions; do not aggregate it merely to remove duplicate order-product combinations.

An association table can be factless when it only records relationships. When it contains measures such as quantity, allocated revenue, or weight, it is a transactional fact table.

Configure the Relationships

In Power BI Desktop:

  1. Open Model view.

  2. Drag Orders[Order ID] onto Order Lines[Order ID].

  3. Set cardinality to One-to-many and cross-filter direction to Single.

  4. Repeat for Products[Product ID] and Order Lines[Product ID].

  5. Confirm that Order Lines is on the many side and both relationships are active.

Keep dimension attributes on the one side of each relationship. Customer and order attributes belong in Orders, while product name, category, and brand belong in Products.

Power BI relationships use one column from each table; composite relationship keys are not supported. If the source has only a composite key, create a deterministic single-column join key in the source or Power Query, or use an existing surrogate key.

Sample Data and Expected Result

Order ID Product ID Quantity Unit Price
O1 P1 2 10
O1 P2 1 20
O2 P2 3 20

A measure such as Revenue = VAR RevenueAmount = SUMX ( 'Order Lines', 'Order Lines'[Quantity] * 'Order Lines'[Unit Price] ) RETURN RevenueAmount produces:

Product ID Revenue
P1 20
P2 80
Total 100
Order ID Revenue
O1 40
O2 60
Total 100

Filtering by P2 selects two order-line rows representing three units. Filtering by O1 selects two rows, and the grand total remains 100 in both visuals.

Direct Many-to-Many Alternative

In an Import-mode model, Power BI supports direct relationships with Many-to-many cardinality under Model view → Manage relationships → New relationship. Direct many-to-many relationships support only single-direction cross-filtering, and their matching join columns may contain repeated values on both sides.

Use direct many-to-many relationships when the source model already requires them and no suitable association fact table exists. They can produce ambiguous results when join columns represent different grains or when multiple relationship paths connect the tables. DirectQuery models do not support direct many-to-many relationships, so the association-table pattern is required there.

Best Practices and Common Pitfalls

  • Define the grain: State what one row represents before removing duplicates or aggregating values. Aggregate only when that is genuinely the business grain.

  • Prefer single-direction filtering: Use bidirectional relationships only when a documented business rule requires them.

  • Validate keys: Check duplicate and null values, and ensure both relationship columns have compatible data types.

  • Test totals and counts: Compare visual results with source-system totals and test filters from every dimension.

  • Prepare data separately: Build association records and derived join keys in the source system or Power Query; create model relationships in Model view.

  • Use measures: Totals, ratios, and business logic generally belong in measures, while calculated columns are appropriate for reusable row-level values.

  • Test security: With row-level security, verify that both dimensions and the associative fact table prevent unauthorized data from being exposed.

  • Publish and operate the model: Publishing preserves the model and relationships. Deploy through a workspace or app when governed release management is required, and configure an on-premises gateway for scheduled refresh from private on-premises sources.

59

How do you implement what-if analysis in Power BI?

What-if analysis in Power BI lets users change assumptions—such as price, sales volume, costs, or exchange rates—and immediately recalculate report outcomes. The usual implementation combines disconnected what-if parameter tables with DAX measures while preserving the original source data.

Read full answer

Applies toMicrosoft Power BI Desktop and Power BI Service (current release)

Core Approach

  • Store changing assumptions in a what-if parameter table.

  • Keep the parameter table disconnected from the data model.

  • Build scenario calculations as measures, not calculated columns.

  • Add parameter slicers and place the scenario measures on the relevant visuals.

  • Display baseline results, scenario results, and variance together.

The parameter is an input to a measure rather than a business dimension, so its table should not have a relationship to the sales fact table.

Create the What-If Parameters

  1. Select Modeling > New parameter > What-if parameter.

  2. Create Price Adjustment % with:

  3. Minimum: -0.20

  4. Maximum: 0.50

  5. Increment: 0.05

  6. Create Volume Adjustment % with the same values.

  7. Format both parameter columns as Percentage.

  8. Select Insert > Slicer, add each parameter field to its slicer, and select only one value at a time.

  9. Select 0% initially for the baseline.

  10. Add [Revenue], [Scenario Revenue], and [Revenue Variance] to the relevant cards and visuals.

Power BI creates a separate table for each parameter. Do not create relationships from these tables to the sales fact table. Because they are disconnected, the slicers do not directly filter Sales; their selected values are read by the measures.

Example Data and Expected Result

Assume the following Sales table contains:

Order Product Quantity Unit Price Revenue
1 A 100 10 1,000
2 B 200 11 2,200
Total 3,200

With a 10% price increase and a 5% volume decrease:

  • Product A: 100 × 0.95 × 10 × 1.10 = 1,045

  • Product B: 200 × 0.95 × 11 × 1.10 = 2,299

  • Scenario revenue: 3,344

  • Variance: 3,344 − 3,200 = 144, or 4.5%

Code Example

Create these DAX measures:

Revenue :=
VAR TotalRevenue =
    SUM ( 'Sales'[Revenue] )
RETURN
    TotalRevenue
Scenario Revenue :=
VAR PriceAdjustment =
    COALESCE (
        SELECTEDVALUE ( 'Price Adjustment %'[Price Adjustment %] ),
        0
    )
VAR VolumeAdjustment =
    COALESCE (
        SELECTEDVALUE ( 'Volume Adjustment %'[Volume Adjustment %] ),
        0
    )
RETURN
    SUMX (
        'Sales',
        'Sales'[Quantity] * ( 1 + VolumeAdjustment )
            * 'Sales'[Unit Price] * ( 1 + PriceAdjustment )
    )
Revenue Variance :=
VAR BaselineRevenue = [Revenue]
VAR AdjustedRevenue = [Scenario Revenue]
RETURN
    AdjustedRevenue - BaselineRevenue
Revenue Variance % :=
VAR BaselineRevenue = [Revenue]
VAR VarianceAmount = [Revenue Variance]
RETURN
    DIVIDE ( VarianceAmount, BaselineRevenue )

SELECTEDVALUE reads the selected parameter from the current filter context. SUMX establishes a row context for each Sales row, while filters from related dimensions remain effective. If an adjustment applies only to selected products or categories, use CALCULATE with an explicit filter expression to modify the filter context before evaluating the measure.

Performance and Data Modelling

  • Use a star schema, with Sales as the fact table and dimensions such as Product, Date, and Region.

  • Keep what-if parameter tables disconnected to avoid unintended filtering relationships.

  • For uniform adjustments across every row, multiply the baseline measure by the two adjustment factors. This is simple and can be efficient, but it is not inherently faster than every SUMX implementation; benchmark the model.

  • Use SUMX when the scenario must recompute each row, such as adjusting quantity before applying price.

  • To vary assumptions by product or category, create a scenario table containing the relevant dimension keys and values, then apply it with CALCULATE. A single disconnected parameter cannot encode multiple rates.

  • Use measures for dynamic scenario outputs because calculated columns do not respond to parameter selections and increase model size.

Report Design and Deployment

  • Show Baseline Revenue, Scenario Revenue, and Variance as KPI cards.

  • Apply conditional formatting to the variance and use labels such as Price +10%.

  • Keep visible assumptions small and use separate report pages for complex scenarios.

  • Power BI Service consumers can change interactive parameter selections without Edit permission. Edit permission is required to change report or model definitions.

  • Publish the report and semantic model together, test Service permissions, and document assumptions and valid parameter ranges.

  • For governed scenarios, maintain approved scenario definitions centrally and distribute them through a dedicated semantic model or deployment pipeline.

60

How can Power BI import Excel and SharePoint files?

Use the Excel Workbook connector for local workbooks and the SharePoint Folder connector for files stored in SharePoint or OneDrive for Business. Authenticate with an organizational account, select the relevant files or sheets in Power Query, and load the results into a properly modeled Power BI semantic model.

Read full answer

Applies toPower BI Desktop (current release) and Power BI Service (current release)

Connect Excel Files in Power BI Desktop

  1. Select Home > Get Data > Excel > Browse.

  2. Choose an .xlsx, .xls, .xlsm, or .xlsb workbook and select Open.

  3. In the Navigator window:

  4. Select a worksheet or Excel table.

  5. Use Ctrl to select multiple objects when required.

  6. Select Transform Data to clean the data in Power Query, or Load to import it directly.

  7. In Power Query Editor, rename the query according to its business purpose, remove unused columns, and verify data types before selecting Close & Apply.

To combine multiple local workbooks, use Home > Get Data > Folder, select the folder, and then use Combine Files in Power Query. To combine several worksheets from one workbook, select them separately in Navigator and use Append Queries in Power Query; Combine Files does not merge worksheets within one workbook.

Connect SharePoint Files in Power BI Desktop

  1. Select Home > Get Data > Online Services > SharePoint Folder.

  2. Enter the SharePoint site or document-library URL, such as https://contoso.sharepoint.com/sites/Sales.

  3. Sign in with an organizational account that can access the files.

  4. Select the document library or required files and select Open.

  5. Select Transform Data to configure the workbook query or Load to import it.

  6. To combine multiple workbooks, open the SharePoint folder query, select Home > Combine Files, and specify the sample file and table.

Microsoft recommends using the SharePoint Folder connector for OneDrive for Business rather than relying on the dedicated OneDrive for Business connector.

Business Example and Expected Result

Suppose two SharePoint workbooks contain the following sales rows:

File Region Amount
Q1 Sales.xlsx East 100
Q1 Sales.xlsx West 50
Q2 Sales.xlsx East 120
Q2 Sales.xlsx West 60

After combining the Sales worksheets, the result is:

Region Amount
East 220
West 110
Total 330

Model the combined data as a fact table. If the source also contains Product and Date, connect those dimensions through a star schema rather than creating a separate fact table for every monthly workbook.

Publish and Refresh in Power BI Service

  • Select Publish in Power BI Desktop and choose a workspace.

  • In Power BI Service, open Workspaces > Semantic models > [model] > Settings and configure Data source credentials.

  • For scheduled refresh from SharePoint sources, install and configure an on-premises data gateway in the tenant and associate it through the semantic model’s Settings > Gateway page.

  • Give the gateway service account access to the SharePoint library and configure the required refresh schedule.

  • Test the gateway and semantic-model refresh before relying on scheduled refreshes.

An Excel workbook uploaded directly to a workspace is not automatically refreshed from its original SharePoint location. Publish the semantic model created with the appropriate source connector instead.

Code Example

Local Excel workbook query, with the query renamed Sales:

let
    // Load the worksheet named Sales.
    Source = Excel.Workbook(
        File.Contents("C:\Data\Sales.xlsx"),
        null,
        true
    ),
    SalesSheet = Source{[Item = "Sales", Kind = "Sheet"]}[Data],
    TypedData = Table.TransformColumnTypes(
        SalesSheet,
        {
            {"Region", type text},
            {"Amount", Currency.Type}
        }
    )
in
    TypedData

SharePoint folder query, with the query renamed SharePointSales:

let
    SiteUrl = "https://contoso.sharepoint.com/sites/Sales",
    FolderUrl = "https://contoso.sharepoint.com/sites/Sales/Shared Documents",
    Source = SharePointFolder(FolderUrl, SiteUrl),
    ExcelFiles = Table.SelectRows(
        Source,
        each [Name] = "Q1 Sales.xlsx" or [Name] = "Q2 Sales.xlsx"
    ),
    // Extract the Sales worksheet from each workbook binary.
    WithTables = Table.AddColumn(
        ExcelFiles,
        "SalesTable",
        each let
            Book = Excel.Workbook([Content], null, true),
            Sheet = Book{[Item = "Sales", Kind = "Sheet"]}[Data]
        in
            Sheet
    ),
    Combined = Table.Combine(WithTables[SalesTable]),
    TypedData = Table.TransformColumnTypes(
        Combined,
        {
            {"Region", type text},
            {"Amount", Currency.Type}
        }
    )
in
    TypedData

DAX measure for the combined table:

Total Sales =
VAR
    SalesAmount = SUM ( SharePointSales[Amount] )
RETURN
    SalesAmount

Best Practices and Common Pitfalls

  • Control the schema: Standardize headers, data types, date formats, and missing-value conventions across files.

  • Reduce file size: Remove unused ranges, columns, formulas, and hidden sheets before importing.

  • Keep paths stable: Use a dedicated SharePoint library and avoid references to temporary local folders.

  • Do not embed credentials: Configure credentials and gateway access through Power BI rather than storing passwords in queries.

  • Separate access layers: SharePoint permissions control source access; Power BI row-level security controls what report users can see.

  • Plan refresh capacity: Large numbers of workbooks increase refresh time and gateway load, so partition data where justified.

  • Design for users: Use a clear star schema, consistent measure names, and straightforward visuals instead of duplicating calculations across visuals.

61

How do you calculate previous-month total sales with Power BI DAX?

A previous-month sales measure uses time intelligence to replace the current month with the preceding calendar month and then evaluates the total sales measure. It should be implemented as a DAX measure so that it responds dynamically to report filters and visual context.

Read full answer

Applies toMicrosoft Power BI Desktop and Power BI Service (current release)

How It Works

The measure relies on two model components:

  • Total Sales: A base measure such as SUM('Fact Sales'[Sales Amount]).

  • A dedicated date table: A contiguous table containing one row per day and every date needed for the reporting periods. Its date column should be marked as the date table in Power BI.

PREVIOUSMONTH('Date'[Date]) returns the dates in the previous calendar month. Passing that table to CALCULATE replaces the current filter on 'Date'[Date] while evaluating [Total Sales].

For example, in a visual filtered to February 2025, the measure evaluates sales for January 2025.

DATEADD('Date'[Date], -1, MONTH) is another common approach, but it shifts every date currently in the filter context by one month. It may omit part of the preceding month when the current context lacks corresponding dates—for example, February has no 29th–31st in a non-leap year. Use PREVIOUSMONTH when the requirement is the complete previous calendar month.

Business Example

Assume the model contains the following sales facts:

Sales Date Sales Amount
2025-01-10 300
2025-01-20 200
2025-01-31 100
2025-02-03 500
2025-02-15 100

When the report is filtered to February 2025:

  • [Total Sales] returns 600.

  • [Total Sales Previous Month] returns 600, representing total January sales.

A card or table using the previous-month measure therefore displays 600.

Code Example

Create the base measure and the previous-month measure in the model:

Total Sales =
SUM ( 'Fact Sales'[Sales Amount] )
Total Sales Previous Month =
VAR PreviousMonthDates =
    PREVIOUSMONTH ( 'Date'[Date] )
VAR PreviousMonthSales =
    CALCULATE (
        [Total Sales],
        PreviousMonthDates
    )
RETURN
    PreviousMonthSales

Add [Total Sales] beside [Total Sales Previous Month] in a table or matrix to show the current and preceding months together.

Best Practices and Common Pitfalls

  • Use a dedicated date table: Include every date in the relevant reporting periods without gaps or duplicates. In Model view, select 'Date'[Date], then choose Table tools > Mark as date table.

  • Create the correct relationship: Relate 'Fact Sales'[Sales Date] to 'Date'[Date] using an active, single-direction, many-to-one relationship from the fact table to the date table.

  • Use a measure, not a calculated column: Measures are evaluated dynamically in filter context. A calculated column is evaluated row by row during refresh and will not respond dynamically when report filters change.

  • Use the date table in visuals and slicers: Build year, month and other date hierarchies from 'Date' rather than using 'Fact Sales'[Sales Date].

  • Confirm the current month context: The result depends on the month represented by the visual or filter. Without a month filter, its result depends on the date context in which it is evaluated.

  • Validate the result: Test the measure in a card and in a table containing year and month fields, then compare it with a manually filtered previous month.

62

How do I create a DAX measure for sales from the top 5 products?

A top 5 products measure ranks products by total sales, then uses CALCULATE to return sales only for those products. This is useful in a card, matrix, or clustered column chart where the business wants to focus on the highest-performing products.

Read full answer

Applies toMicrosoft Power BI Desktop and Power BI Service (current release)

How It Works

The measure uses three important DAX operations:

  • ADDCOLUMNS calculates total sales for every product in the product dimension.

  • TOPN selects the five products with the highest sales.

  • CALCULATE applies the selected product filter to the base sales measure.

ALL('Product') removes product filters while calculating the ranking. This is important when Product Name is placed on rows in a visual: otherwise, each row could rank itself independently instead of being compared with all products. Filters from other tables, such as date, region, or store, remain in the calculation context.

Code Example

Create the base measure first by selecting Modeling > New measure and entering:

Total Sales =
SUM ( Sales[Sales Amount] )

Then create the top-products measure:

Top 5 Product Sales =
VAR ProductSales =
    ADDCOLUMNS (
        ALL ( 'Product' ),
        "@ProductSales", [Total Sales]
    )
VAR TopProducts =
    TOPN (
        5,
        ProductSales,
        [@ProductSales],
        DESC
    )
RETURN
    CALCULATE (
        [Total Sales],
        KEEPFILTERS ( TopProducts )
    )

Use the measure in a visual:

  • Add Product[Product Name] to rows or the axis.

  • Add [Top 5 Product Sales] to the visual values.

  • Add the same measure to a card when only the total sales for the top five products is required.

Sample Data and Result

Assume the following data exists in Sales:

Product Sales Amount
Alpha 120
Beta 110
Gamma 95
Delta 80
Epsilon 75
Zeta 60

The ranking is:

Rank Product Sales
1 Alpha 120
2 Beta 110
3 Gamma 95
4 Delta 80
5 Epsilon 75

A card using [Top 5 Product Sales] returns:

480

If Product Name is included in a matrix, only Alpha through Epsilon display a value; Zeta is filtered out.

Performance and Modeling

  • Use a star schema: Keep product attributes in a dimension table such as Product, with one row per product and a one-to-many relationship to Sales.

  • Use a unique key: Product[ProductKey] should identify each product uniquely. Product names may be duplicated or changed.

  • Measure sales at the fact level: SUM(Sales[Sales Amount]) is usually more efficient than creating a calculated column for every sales transaction.

  • Filter early: Slicers and visual-level filters for date, region, and category reduce the number of rows processed and make the ranking more relevant to the user.

  • Use KEEPFILTERS: This preserves valid intersections with the current filter context and prevents the top-products filter from unexpectedly replacing other filters.

Common Pitfalls

  • TOPN can return more than five rows when several products tie at the fifth-highest sales value. If exactly five rows are required, add a deterministic tie-breaker such as Product Key.

  • ALL('Product') intentionally ignores product slicers when determining the ranking. If a product slicer should restrict which products are eligible for the top five, replace it with a ranking expression that preserves that requirement.

  • A calculated column cannot dynamically calculate the top five across all current filters. Use a measure so the ranking responds correctly to slicers, visual filters, and row or column context.

63

How do you troubleshoot wrong totals in Power BI reports?

Wrong totals in Power BI usually come from a measure being evaluated under unexpected filters, a fact table with the wrong row grain, an incorrect relationship, or report-level and row-level security. Troubleshooting should compare the visual result with the measure evaluated independently, then isolate filters and model behavior before changing the report.

Read full answer

Applies toPower BI Desktop and Power BI Service (current release)

Troubleshooting Workflow

  1. Define the expected result

  2. Confirm the business meaning: total revenue, invoice value, quantity, or a distinct count.

  3. Identify the expected scope, such as all 2025 sales or sales for one customer.

  4. Check whether the visual is intended to show a grand total, subtotal, or filtered total.

  5. Inspect the visual

  6. Check slicers, report-page filters, visual-level filters, bookmarks, and drill-down state.

  7. Select the visual and use View > Filters or the Filters pane to see which fields affect it.

  8. Temporarily remove filters and compare the result with the original visual.

  9. Use View > Performance analyzer to inspect the DAX queries generated by the visual.

  10. Test the measure independently

  11. Create a blank page and place the measure in a card or small table.

  12. Compare the measure result with the same measure in the affected visual.

  13. If the card is correct but the visual is wrong, investigate filters, hierarchies, visual interactions, or a calculated column used instead of a measure.

  14. Validate the data model

  15. Open Model view and check each relationship.

  16. Confirm that dimensions filter the fact table using a one-to-many relationship with single-direction filtering.

  17. Check for unexpected many-to-many relationships, bidirectional filters, or relationships created from the wrong fields.

  18. Use Power Query Editor to verify the fact table’s row grain and check for duplicated rows.

Common Causes

  • Measure filters: A measure responds to slicers, page filters, visual filters, and relationships. A total may be correct in isolation but filtered in the report.

  • Calculated columns: A calculated column is evaluated during refresh in row context and then stored. It does not dynamically respond to slicers or relationship filters. Prefer a measure for aggregations.

  • Incorrect fact-table grain: If one sales line appears multiple times, SUM('Sales'[Amount]) double-counts it.

  • Relationship errors: An incorrect key relationship can make a dimension filter the wrong fact table or propagate filters unexpectedly.

  • Row-level security (RLS): Users may see a correct total for their permitted data but a different total under a role.

  • Stale data: A failed or delayed refresh can leave the report showing older source values.

  • Visual totals: Totals are evaluated from the model in the visual’s filter context. They are not necessarily the arithmetic sum of displayed category or subtotal values, so confirm the intended scope and aggregation.

Validate With Sample Data

Suppose the intended fact table is one row per invoice line:

InvoiceLineID CustomerID ProductID Amount
L001 C01 P01 100
L002 C01 P02 50
L003 C02 P01 200

The expected result is:

Scope Expected total
All sales 350
Product P01 300
Product P02 50
Customer C01 150

If an incorrect source join duplicates L001, SUM returns 450 instead of 350. Compare COUNTROWS('Sales') with the number of distinct invoice-line IDs and inspect the source query before modifying the model.

Code Example

Create these measures using Modeling > New measure:

Sales Amount :=
VAR
    Amount = SUM ( 'Sales'[Amount] )
RETURN
    Amount

This measure should return 350 for the sample data when no filters are active.

Sales Amount (Ignore Customer Filter) :=
VAR
    Amount =
        CALCULATE (
            [Sales Amount],
            REMOVEFILTERS ( 'Customer' )
        )
RETURN
    Amount

Use the second measure to determine whether customer filters are causing the discrepancy. For example, if the first measure returns 150 for Customer C01 and the second returns 350, the customer relationship and filters are functioning as expected.

Best Practices and Design Checks

  • Design the model as a star schema, with a one-to-many relationship from dimensions to the fact table.

  • Keep the fact table at its lowest intended grain and remove accidental duplicates at the source or with a carefully validated Power Query transformation.

  • Use measures for report-time calculations; use calculated columns only when a row-level value must be stored.

  • Test totals with a small, independently verified dataset and document the expected business result.

  • Check filters and RLS before changing DAX. In Power BI Desktop, use Modeling > Manage roles > Test as role; in Power BI Service, verify the role assignment and the user’s view of the report.

  • Avoid bidirectional relationships unless a documented business requirement justifies them. For complex many-to-many relationships, use a bridge table or a model that explicitly defines the intended filter behavior.

  • After correcting the model, refresh the data and re-test both Desktop and Service versions.

64

How do you optimize a slow Power BI DirectQuery report?

A slow DirectQuery report should be optimized end to end: identify whether the latency comes from the data source, Power BI’s formula engine, or visual rendering, then improve source queries, model design, DAX, and visual interactions. The main objective is to return fewer rows, aggregate at the source, and avoid unnecessary cross-filtering.

Read full answer

Applies toMicrosoft Power BI Desktop and Power BI Service (current release)

Diagnose the Bottleneck

Start with evidence rather than changing measures blindly:

  1. Open Optimize → Performance Analyzer in Power BI Desktop.

  2. Record the report and identify the slow visual or interaction.

  3. Inspect the visual’s DAX query and underlying source-query duration.

  4. Determine where time is spent:

  5. Source latency: The database, view, indexes, or network is slow.

  6. Formula-engine latency: The model or DAX requires substantial processing.

  7. Visual latency: The page contains too many records, visuals, or complex interactions.

  8. Use source-side query tools, XE traces, or a tool such as DAX Studio where appropriate to inspect the generated SQL.

In Power BI Service, also distinguish report-query latency from gateway delays and capacity throttling.

Optimize the Source

DirectQuery may generate a source query for each visual interaction, so source efficiency directly affects users.

  • Push filtering and aggregation into the source through views, indexed columns, or source-native SQL.

  • In Power Query, confirm transformations are folded down by checking View Native Query for supported connectors.

  • Index join keys and commonly filtered dimension columns, but avoid indexing every column.

  • Partition very large fact tables where the source supports it.

  • Pre-aggregate common business views when users do not need transaction-level detail.

  • Avoid unnecessary row-by-row transformations or retrieving large intermediate result sets.

  • Have database administrators validate execution plans and source query performance rather than relying only on Power BI changes.

Improve the Data Model

Use a conventional star schema:

  • Create one fact table, such as FactSales, containing measures and foreign keys.

  • Create separate dimensions, such as Product and Date.

  • Use one-to-many relationships from each dimension to the fact table.

  • Keep the filter direction flowing from dimensions to facts.

  • Avoid bidirectional relationships unless the business requirement is explicit and tested.

  • Use compact, consistent keys and mark a complete date dimension as the date table.

  • Avoid exposing high-cardinality fact fields as slicers or visual dimensions.

DirectQuery is designed for large datasets, but a poorly shaped model can still generate expensive joins and repeated intermediate calculations.

Optimize DAX and Visuals

Measures are evaluated in filter context, while calculated columns are evaluated in row context. In CALCULATE, a Boolean filter replaces existing filters on the same column unless KEEPFILTERS is used.

  • Prefer measures over calculated columns for business calculations.

  • Use VAR and RETURN to make repeated logic readable and avoid unnecessary recalculation.

  • Minimize nested CALCULATE, context transitions, and filters over large fact tables.

  • Filter dimensions early and aggregate facts before returning detail.

  • Limit visuals per page and avoid displaying thousands of fact rows.

  • Replace detail tables with summaries, drill-through pages, or paginated reports.

  • Test slicer combinations because each additional filter combination can affect query cost.

Code Example

Consider this small dataset:

OrderDate Product Amount
2025-01-05 Mountain Bike 1,000
2025-01-10 City Bike 200
2025-01-12 Helmet 300

The Product dimension contains:

Product Category
Mountain Bike Bikes
City Bike Bikes
Helmet Accessories

These measures produce the expected business results:

Total Revenue = 
SUM ( 'FactSales'[Amount] )

Bikes Revenue = 
VAR BikesSales = 
    CALCULATE (
        [Total Revenue],
        KEEPFILTERS ( 'Product'[Category] = "Bikes" )
    )
RETURN
    BikesSales

Expected results:

Measure Result
Total Revenue 1,500
Bikes Revenue 1,200
Accessories Revenue 300

KEEPFILTERS ensures that an existing category filter is preserved, rather than being overwritten by the "Bikes" condition.

Best Practices and Rollout

  • Optimize with representative data volumes, not only a small development sample.

  • Validate improvements in Power BI Desktop and Power BI Service.

  • Document source indexes, model relationships, DAX measures, and accepted refresh times.

  • Schedule refreshes away from peak analytical periods when possible.

  • Keep expensive source operations in the database or governed views instead of duplicating them across measures.

  • Review performance after source schema changes, new relationship paths, increased fact-table volume, or additional slicers.

  • Treat model and source changes through normal database governance and deployment processes rather than as isolated report tweaks.

Sign in to keep reading

You've opened 2 full answers. Sign in to read all 64 Power BI answers, mark what you know and pick up where you left off.

Report a mistake

What's wrong?

All Data Analytics & BI subjects