How to create an automatic diagnostic dashboard with Copilot in Power BI

João Barros 11 de November de 2026 4 min read

This tutorial shows how to create an automatic diagnostic dashboard with Copilot in Power BI that identifies data quality and refresh performance issues. Having a diagnostic dashboard helps detect and prioritize fixes without manually combing through reports or pipelines.

Prerequisites

Account with Power BI Pro or Premium and access to Copilot in Power BI.
Report or dataset published to the Power BI service with refresh history and quality fields (e.g.: nulls, duplicates).
Edit permissions in the workspace where the report/dataset is published.

Step 1: Identify relevant diagnostic metrics

Before using Copilot, decide which indicators you want to monitor. Useful examples: percentage of null values per column, average refresh time, number of refresh failures in the last month, and percentage of duplicate rows. This list guides the prompts you will use in Copilot.

Step 2: Create DAX measures for quality and performance metrics

In Power BI Desktop or the service, add simple DAX measures that expose the metrics. These measures will be the foundation of the dashboard and the automatic diagnosis.

-- Exemplo: Percentagem de nulos numa coluna CustomerID
Nulls_CustomerID = DIVIDE(CALCULATE(COUNTROWS(table), ISBLANK(table[CustomerID])), COUNTROWS(table))

-- Exemplo: Contagem de falhas de refresh (assumindo tabela RefreshLog com Status)
Refresh_Failures_Last30 = CALCULATE(COUNTROWS(RefreshLog), RefreshLog[Status] = "Failed", RefreshLog[RefreshDate] >= TODAY()-30)

-- Exemplo: Tempo médio de refresh
Avg_Refresh_Time = AVERAGE(RefreshLog[DurationSeconds])

-- Exemplo: Percentagem de duplicados (exemplo simplificado)
Duplicate_Rate = DIVIDE(CALCULATE(COUNTROWS(table), COUNTROWS(FILTER(table, table[Key] = EARLIER(table[Key])) ) > 1), COUNTROWS(table))

Step 3: Publish the report and prepare a diagnostic prompt

Publish the report with these measures to the workspace. Then prepare a clear prompt for Copilot asking for an automatic analysis of the metrics and actionable suggestions. The prompt should mention which measures and the objective (identify causes and prioritize fixes).

Prompt exemplo:
"Analisa as seguintes métricas deste relatório: Nulls_CustomerID, Refresh_Failures_Last30, Avg_Refresh_Time, Duplicate_Rate. Identifica as principais causas prováveis para cada métrica fora de thresholds (0.05 para Nulls, >3 falhas/30 dias, Avg_Refresh_Time>600s, Duplicate_Rate>0.02). Sugere 3 ações prioritárias, incluindo queries/transformações a aplicar no Power Query ou verificações na origem."

Step 4: Run Copilot on the report and refine the output

Open the report in the Power BI service and enable the Copilot pane. Paste the prompt and request an analysis. Copilot will read the measures and return explanations and suggestions. Evaluate the suggestions and ask Copilot for clarifications to obtain concrete steps (e.g.: M code for Power Query).

Step 5: Apply suggested fixes (Power Query example)

When Copilot suggests transformations, integrate them in Power Query in Power BI Desktop. Common example: remove rows with null CustomerID and eliminate duplicates keeping the most recent row.

// Exemplo M: remover nulos e deduplicar por Key mantendo a linha com Max Date
let
  Fonte = Excel.Workbook(File.Contents("dados.xlsx"), null, true),
  Tabela = Fonte{[Name="Sheet1"]}[Data],
  FiltrarNulos = Table.SelectRows(Tabela, each [CustomerID] <> null),
  Ordenar = Table.Sort(FiltrarNulos,{{"Date", Order.Descending}}),
  RemoverDuplicados = Table.Distinct(Ordenar, {"Key"})
in
  RemoverDuplicados

Step 6: Optional automation with alerts and scheduling

After updating the report/dataset, you can create alerts in the Power BI service for critical metrics (for example Refresh_Failures_Last30 > 0). Also set a more frequent refresh schedule if Avg_Refresh_Time is acceptable.

Verify the result

Confirm that the measures reflect the changes: percentage of nulls decreased, Duplicate_Rate reduced, and the number of refresh failures updated. Test the prompt in Copilot again to see if recommendations changed. Also check whether alerts fire when applicable.

Conclusion

With these steps you have a diagnostic dashboard with Copilot in Power BI that identifies issues and generates concrete actions. Next steps: automate the prompt via API (when available) and integrate additional logs. Tip: keep thresholds documented to reduce false positives.