Dyota's blog

Agent skill - query DAX

Ever had to flick through tables back and forth in Power BI, filtering tables, matching up key IDs, copying into Excel, analysing for hours?

What if an AI could do all that for you?

This skill is exactly that. These are the requirements:

The skill is here. Paste this into SKILL.md:

---
name: pbi-query
description: >
  Query a running Power BI Desktop instance using DAX. Use this skill whenever the user wants
  to investigate, audit, validate, or explore data in a Power BI model — even if they don't
  say "DAX" or "query". Trigger on investigative briefs like "check what revenue looks like by
  project", "does this measure return the right values?", "why is this number off?", or any
  question that requires pulling actual data out of an open Power BI file. The skill handles
  query formulation, execution against the local Analysis Services instance, and interpretation
  of results. Always use this when the user is asking questions that can be answered by
  querying a live Power BI model.
---

## Overview

You have access to a PowerShell script that connects to the Analysis Services instance spawned
by Power BI Desktop and executes DAX queries. The user will give you an investigative brief.
Your job is to formulate DAX queries, run them, interpret the results, and report back.

## Script location

```
scripts/Invoke-PBIQuery.ps1
```

The `packages/` folder containing the ADOMD.NET assembly lives alongside the script and is
referenced via `$PSScriptRoot` — do not move or reference it separately.

## Workflow

### 1. Detect instances

Before running any query, check what PBI instances are running:

```powershell
pwsh -File "\Invoke-PBIQuery.ps1" -ListInstances
```

- **Single instance**: proceed automatically — no need to ask the user.
- **Multiple instances**: present the CSV output to the user (ProcessId, Port, StartTime) and
  ask which one to target. Then use `-Port ` or `-ProcessId ` in all subsequent calls.

### 2. Formulate and run DAX queries

Use `EVALUATE` expressions. Pass them via `-Query`:

```powershell
pwsh -File "\Invoke-PBIQuery.ps1" -Query "EVALUATE " [-Port ]
```

Keep queries focused. Run multiple targeted queries rather than one broad one — it's easier
to interpret and cheaper on tokens. Common patterns:

```dax
-- Row count / existence check
EVALUATE ROW("Count", COUNTROWS('TableName'))

-- Grouped summary
EVALUATE SUMMARIZECOLUMNS(
    'DimTable'[Column],
    "Metric", [MeasureName]
)

-- Filtered slice
EVALUATE CALCULATETABLE(
    SUMMARIZECOLUMNS('Fact'[Key], "Val", [Measure]),
    'Dim'[Field] = "Value"
)

-- Spot check a measure in a specific context
EVALUATE ROW("Result", CALCULATE([MeasureName], 'Dim'[Field] = "Value"))
```

### 3. Interpret results

Output is CSV. Parse it mentally — you don't need to display raw CSV to the user unless they
ask. Summarise findings in plain language, flag anomalies, and draw conclusions relative to
the brief.

If a query errors (ADOMD exception), examine the error message: it usually identifies the
offending table or column name. Correct and retry — don't surface raw exception text to the
user unless it's unresolvable.

### 4. Iterate

Run follow-up queries as needed to answer the brief fully. There is no query limit — keep
going until you have a clear answer or have exhausted the available data.

## Notes

- Power BI Desktop must be open with the target `.pbix` or `.pbip` file loaded.
- The script connects to the embedded local Analysis Services — no Power BI Service, no
  credentials required.
- Column names in ADOMD results are prefixed with the table name (e.g. `[TableName[Column]]`
  in the CSV header). Account for this when referencing columns.
- If you're unsure what tables or measures exist, query `$SYSTEM.TMSCHEMA_TABLES` or
  `$SYSTEM.TMSCHEMA_MEASURES` via DMV:
  ```dax
  EVALUATE SELECTCOLUMNS(INFO.TABLES(), "Name", [Name])
  EVALUATE SELECTCOLUMNS(INFO.MEASURES(), "Table", [TableID], "Name", [Name], "Expression", [Expression])
  ```

The below is the code for Invoke-PBIQuery.ps1:

# Generated by GitHub Copilot (Claude Sonnet 4.6) on 2026-06-04
# Queries the local Analysis Services instance spawned by Power BI Desktop.
# PBI Desktop must be open with the target file.
#
# Usage:
#   .\Invoke-PBIQuery.ps1 -Query "EVALUATE ROW(""Test"", 1)"
#   .\Invoke-PBIQuery.ps1 -Query (Get-Content .\myquery.dax -Raw)

using namespace System.Data
using namespace Microsoft.AnalysisServices.AdomdClient

param(
    [Parameter(Mandatory)]
    [string] $Query,

    [string] $AdomdPath = "$PSScriptRoot\packages\Microsoft.AnalysisServices.AdomdClient.NetCore.retail.amd64.19.84.1\lib\netcoreapp3.0\Microsoft.AnalysisServices.AdomdClient.dll"
)

[void][System.Reflection.Assembly]::LoadFrom($AdomdPath)

# Auto-detect msmdsrv port
[int] $port = -1
$msmds = Get-Process -Name msmdsrv -ErrorAction SilentlyContinue | Select-Object -First 1
if ($null -eq $msmds) { throw "msmdsrv not found. Is Power BI Desktop open?" }

$netstatLines = netstat -ano | Where-Object { $_ -match "LISTENING" -and $_ -match "\s$($msmds.Id)$" }
foreach ($line in $netstatLines) {
    if ($line -match "127\.0\.0\.1:(\d+)") {
        $port = [int]$matches[1]
        break
    }
}
if ($port -eq -1) { throw "Could not detect msmdsrv port for PID $($msmds.Id)" }

Write-Host "Connecting to localhost:$port ..." -ForegroundColor Cyan

[string] $connStr = "Data Source=localhost:$port"
[AdomdConnection] $conn = [AdomdConnection]::new($connStr)

try {
    $conn.Open()
    [AdomdCommand] $cmd = $conn.CreateCommand()
    $cmd.CommandText = $Query
    [AdomdDataAdapter] $adapter = [AdomdDataAdapter]::new($cmd)
    [DataSet] $ds = [DataSet]::new()
    [void]$adapter.Fill($ds)
    $ds.Tables[0] | Format-Table -AutoSize
}
finally {
    $conn.Close()
}

The dependencies are these: