Integrating Copilot Studio Chatbot with Power BI Semantic Models for Natural Language KPI Queries

Option 1 (recommended): Fabric Data Agent consumed in Copilot Studio. Create a Data Agent in Fabric and add your Power BI semantic model as a data source (works best on models with well-named measures and descriptions). The Data Agent does the NL→DAX translation, executes against the model, and returns a grounded answer. Then in Copilot Studio, add the Data Agent as a connected agent/tool — your chatbot routes KPI questions to it conversationally. Docs: https://learn.microsoft.com/en-us/fabric/data-science/data-agent-microsoft-copilot-studio, https://learn.microsoft.com/en-us/fabric/data-science/concept-data-agent, and a full step-by-step lab: https://microsoft.github.io/mcs-labs/labs/data-fabric-agent/.

Option 2: the remote Power BI MCP server. Microsoft hosts an MCP endpoint (https://api.fabric.microsoft.com/v1/mcp/powerbi) that exposes schema discovery, NL→DAX generation, and query execution as tools any MCP-capable agent — including Copilot Studio agents that support MCP tools — can call. Requires the tenant setting "Users can use the Power BI Model Context Protocol server endpoint (preview)" plus Build permission on the model. Docs: https://learn.microsoft.com/en-us/power-bi/developer/mcp/remote-mcp-server-get-started and the https://learn.microsoft.com/en-us/power-bi/developer/agentic/power-bi-agentic-overview.

Best practices regardless of path: prep the semantic model for AI — clear measure/column names, descriptions, synonyms, hide technical columns, mark AI/Q&A metadata (see https://learn.microsoft.com/en-us/power-bi/create-reports/copilot-semantic-models); note RLS is enforced with user auth but NOT with service-principal auth on the MCP path; and scope the agent to a curated set of KPI measures rather than the whole model to keep answers accurate.

Keep "Run a query against a dataset" only as a fallback for a handful of fixed, parameterized KPI queries (pre-written DAX behind topic slots) — it's reliable for canned metrics but wrong as the engine for open-ended NL questions.