Why SQL Queries Behave Differently in Power BI Than in SSMS

A common and frustrating experience for many Power BI users goes something like this:
“My SQL query runs perfectly in SSMS, but when I use it in Power BI, the results change — or the performance drops.”

At first glance, this feels illogical.
After all, Power BI is using the same database, the same SQL Server, and often the same query.

So why does the behaviour change?

The short answer is: Power BI does not interact with SQL Server the same way SSMS does. This article explains the most common reasons SQL queries behave differently in Power BI than in SSMS, based on real reporting environments and recurring troubleshooting scenarios.

1. SSMS Runs Queries Once — Power BI Runs Them Repeatedly

When you run a query in SSMS:

• It executes once
• You see the result
• The session ends

Power BI behaves very differently.

Depending on the mode (Import or DirectQuery), Power BI may:

• Execute the query multiple times
• Rewrite parts of the query
• Add filters dynamically
• Run variations of the same query during refresh or interaction

A query that feels “fast” in SSMS may become expensive when executed repeatedly under Power BI’s query patterns.

2. Power BI Often Rewrites Your SQL

One of the biggest surprises for many users is that Power BI does not always send your SQL to SQL Server exactly as written.

Power BI may:

• Wrap your query inside another query
• Apply additional filters
• Push transformations differently depending on query folding

This means:

• Execution plans can change
• Index usage may differ
• Performance characteristics shift

What you test in SSMS is often not the final query SQL Server receives from Power BI.

3. Query Folding Changes Behaviour

Query folding is one of Power BI’s most powerful — and misunderstood — features.

In simple terms:

• Power BI tries to push transformations back to SQL Server
• But only when it can safely do so

When folding breaks:

• Some logic runs in SQL
• Some logic runs in Power BI
• Data volume and performance change

A query that looks identical in SSMS may behave differently in Power BI because only part of it is being executed at the database level.

4. Power BI Applies Filters You Didn’t Write

Power BI adds filters automatically.

Examples include:

• Report-level filters
• Page-level filters
• Visual-level filters
• Slicer interactions

These filters are translated into SQL conditions at runtime.

As a result:

• Row counts change
• Execution paths change
• Results appear inconsistent compared to static SSMS runs

In SSMS, you control the entire query.
In Power BI, the report controls it.

5. Import Mode vs DirectQuery Changes Everything

SQL behaviour varies significantly depending on how Power BI connects to SQL Server.

In Import mode:

• SQL is used primarily during refresh
• Queries are executed in bulk
• Performance issues often appear during refresh, not interaction

In DirectQuery mode:

• SQL is executed on every interaction
• Small inefficiencies become very visible
• Concurrency and indexing matter far more

A query tested in SSMS doesn’t reflect how it will behave under continuous DirectQuery execution.

6. Data Types and Implicit Conversions

Another subtle difference comes from data types.

Power BI:

• Interprets data types during ingestion
• May introduce implicit conversions
• Handles NULLs and text values differently

These differences can cause:

• Filter mismatches
• Join inconsistencies
• Unexpected row exclusions

In SSMS, you see exactly what SQL Server returns.
In Power BI, the data passes through an additional interpretation layer.

7. Context Matters More in Power BI

SSMS is context-free. Power BI is not.

Power BI introduces:

• Model relationships
• Filter propagation
• User interaction context

Even when SQL returns the same base data, Power BI’s model context can make results appear different — especially when measures and relationships are involved.

Understanding this interaction between SQL and Power BI is critical when diagnosing “mismatched” results. For readers interested in seeing how these layers interact in real reporting scenarios, this Power BI + SQL approach is explained here: Power BI with SQL

What This Means in Practice

When debugging differences between Power BI and SSMS, it helps to:

• Capture the actual SQL sent by Power BI
• Check query folding behaviour
• Review filters applied by the report
• Compare execution plans, not just run times

Most issues are not bugs — they are design and interaction differences.

Final Thought

SQL behaving differently in Power BI than in SSMS is not a flaw.

It’s a consequence of:

• Dynamic filtering
• Query rewriting
• Execution context
• Analytical workloads

Once you understand how Power BI consumes SQL, these differences stop being mysterious — and start becoming predictable.

Learning How Power BI Actually Executes SQL

For those who want a clearer understanding of how Power BI interacts with SQL Server in real reporting environments, the Power BI + SQL course by ExcelGoodies focuses on execution patterns, performance behaviour, and design decisions that matter in practice.

Check the Upcoming batch details
 

Editor’s Note

This article is based on recurring troubleshooting scenarios where SQL results appeared inconsistent between SSMS and Power BI. The intent is to highlight interaction patterns rather than isolated query issues.

Insights compiled with inputs from the ExcelGoodies Trainers & Power Users Community.
 

MS-SQL