Power BI Metrics Pro Tip: Refresh all metrics script

[cebaylis]

Run this PowerShell script to refresh all connected metrics on a scorecard. While you can always individually refresh connected metrics in the Connections tab of the metric details pane, this script can save time if you want to refresh all connections at once, on one or more scorecards.

How to use 

1. Copy the script below to a new PowerShell file and save it. For example, "refresh-all-metrics.ps1". 

2. Open a new PowerShell window and run the script.

   1. The script will install MicrosoftPowerBIMgmt module if not present on the machine.

   2. You will be prompted to log in to Power BI. 

3. Enter the scorecard id you want to refresh. You can copy the scorecard id from the url when viewing your scorecard. 

4. If you have multiple scorecards to refresh, continue entering ids. If not, enter Q to quit.

Script

$ErrorActionPreference = "Stop"

$api = "https://api.powerbi.com"

if (!(Get-Module -ListAvailable -Name MicrosoftPowerBIMgmt)) {
    try {
        Install-Module -Scope CurrentUser -Name MicrosoftPowerBIMgmt -AllowClobber -Confirm:$False -Force  
    } catch [Exception] {
        $_.message 
        exit
    }
}

Login-PowerBI
$token = (Get-PowerBIAccessToken)["Authorization"]

function GetScorecard($scorecardId) {
    Write-Host "Retrieving scorecard: " -NoNewLine
    $response = Invoke-WebRequest `
        -Uri "$api/v1.0/myorg/scorecards($scorecardId)?`$expand=goals" `
        -Headers @{ "Authorization"=$token }
    Write-Host -ForegroundColor Green OK
    $scorecard = $response.Content | ConvertFrom-Json
    return $scorecard
}

function RefreshGoalValueConnection($scorecardId, $goalId) {
    $response = Invoke-WebRequest `
        -Method Post `
        -Uri "$api/v1.0/myorg/scorecards($scorecardId)/goals($goalId)/RefreshGoalCurrentValue()" `
        -Headers @{ "Authorization"=$token }
}

function RefreshGoalTargetConnection($scorecardId, $goalId) {
    $response = Invoke-WebRequest `
        -Method Post `
        -Uri "$api/v1.0/myorg/scorecards($scorecardId)/goals($goalId)/RefreshGoalTargetValue()" `
        -Headers @{ "Authorization"=$token }
}

function Run($scorecardId) {
    $scorecard = GetScorecard -scorecardId $scorecardId

    $connectedGoals = @($scorecard.goals | where { $_.valueConnection -or $_.targetConnection })

    Write-Host "Found $($connectedGoals.Count) connected metric(s) in scorecard '$($scorecard.name)'."

    if ($connectedGoals.Count -gt 0) {
            foreach ($goal in $connectedGoals) {
                Write-Host "Refreshing connections for metric '$($goal.name)'"
                if ($goal.valueConnection) {
                    Write-Host -NoNewline "   Value connection:  "
                    RefreshGoalValueConnection -scorecardId $scorecardId -goalId $goal.id
                    Write-Host -ForegroundColor Green OK
                }
                if ($goal.targetConnection) {
                    Write-Host -NoNewline "   Target connection: "
                    RefreshGoalTargetConnection -scorecardId $scorecardId -goalId $goal.id
                    Write-Host -ForegroundColor Green OK
                }
            }
    }
}

function ShowPrompt() {
    while ($true) {
        Write-Host -ForegroundColor Yellow "Refresh metrics utility"
        
        $scorecardId = Read-Host -Prompt "Enter scorecard id, or Q to quit"
        if ($scorecardId -eq "q") {
            return
        }
        if (!$scorecardId) {
            Write-Error "Invalid scorecard id"
        }

        Run -scorecard $scorecardId
        Write-Host "`n"
    }
}

ShowPrompt