How to filter anomalies with Copilot in Power BI: step by step

João Barros 07 de August de 2026 4 min read

We'll use Copilot in Power BI to identify and filter anomalies in a sales table, making it easier to analyze real trends without noise. This is useful when you want to automate outlier detection and apply dynamic filters for reports and models.

Prerequisites

Account with access to Copilot in Power BI and permissions to edit reports.
Power BI report with a simple table: columns Date, Product, SalesAmount.
Basic knowledge of Power BI Desktop / Service and DAX measures.

Step 1: Prepare the table and publish

Open the file in Power BI Desktop and confirm that the sales table has the minimum columns (Date, Product, SalesAmount). If necessary, create a sample table. Publish the report to the Power BI Service so Copilot can access the model.

// Exemplo de dados mínimos (CSV) para importar:
Date,Product,SalesAmount
2026-01-01,Produto A,100
2026-01-02,Produto A,120
2026-01-03,Produto A,5000
2026-01-04,Produto B,80
2026-01-05,Produto B,90

Step 2: Start Copilot and describe the task

In the Power BI Service open the published report and enable Copilot. Write a clear prompt that describes the task: detect anomalies in SalesAmount by Product and create a filter that excludes those values. Explain what you consider anomalous (e.g.: points above 3 standard deviations or the 99th percentile).

Exemplo de prompt:
"Deteta anomalias na coluna SalesAmount por Product. Considera anomalias valores acima de 3 desvios padrão e acima do percentil 99. Cria uma nova coluna booleana IsAnomaly e um filtro no relatório para excluir anomalias."

Step 3: Validate Copilot's proposal

Copilot will suggest DAX and/or model changes. Read the suggested expression carefully and confirm that the detection logic matches what you asked for. Common errors: using a global mean instead of by Product; applying inadequate thresholds for small samples.

// Exemplo de DAX sugerido que poderás ajustar:
IsAnomaly =
VAR AvgProd = CALCULATE(AVERAGE(Sales[SalesAmount]), ALLEXCEPT(Sales, Sales[Product]))
VAR StdDevProd = CALCULATE(STDEVX.P(Sales, Sales[SalesAmount]), ALLEXCEPT(Sales, Sales[Product]))
RETURN
ABS(Sales[SalesAmount] - AvgProd) > 3 * StdDevProd

Step 4: Create the IsAnomaly column manually (if you prefer)

If you prefer to control the code, create the column in Power BI Desktop with the adjusted DAX. This ensures you understand the logic and can adapt the threshold or use percentiles.

// DAX alternativo com percentil aproximado:
IsAnomaly =
VAR P99 =
    PERCENTILEX.EXC(
        FILTER(Sales, Sales[Product] = EARLIER(Sales[Product])),
        Sales[SalesAmount], 0.99)
RETURN
Sales[SalesAmount] > P99

Step 5: Create a report filter to exclude anomalies

In Power BI Desktop or in the Service add a slicer or apply a visual-level/page-level filter using the IsAnomaly column = FALSE to view the data without outliers. Ask Copilot to create a visual with and without anomalies for comparison if you need automatic documentation.

// No Power BI: arrasta IsAnomaly para Filters e define como FALSE
// Ou cria um slicer com IsAnomaly e selecciona FALSE

Verify the result

Confirm that the rows marked as anomaly correspond to very high or atypical values by Product. Compare visuals before and after (e.g.: time series of SalesAmount) to validate that removal improves readability. Also check counts by Product to ensure you're not excluding too many points.

Conclusion

After filtering anomalies with Copilot in Power BI you have a cleaner report focused on the real trend. Next steps: adjust thresholds by Product, apply robust methods (median + MAD) or automate an ETL flow that flags anomalies before load. Tip: ask Copilot "why were these rows marked" to get an explanation of the logic applied.