Creating a dynamic calculated column based on 'Parametised' data source

You can use the Power BI REST API to update the calculated column in your report. You can use PowerShell to call the API and pass the appropriate parameters to update the column.

First, you'll need to get the report ID and dataset ID of the report you want to update. You can do this using the Power BI API or by navigating to the report in Power BI and looking at the URL.

Then, you can use the Update Dataset API endpoint to update the calculated column. In the request body, you can include the updated DAX expression for the calculated column.

Here's an example PowerShell script to update the calculated column:

$ReportId = "<your report ID>"
$DatasetId = "<your dataset ID>"
$Token = "<your access token>"

$Headers = @{
"Content-Type" = "application/json"
"Authorization" = "Bearer $Token"
}

$Body = @{
updateDetails = @{
resourcePackage = @{
properties = @{
content = @{
DataTransformations = @{
Transformations = @{
CalculationGroups = @{
[guid]::NewGuid().ToString() = @{
Tables = @{
Data = @{
CalculatedColumns = @{
Area = "SWITCH ( TRUE(), CONTAINSSTRING(Data[Service],`"Dev`"),`"Test`", CONTAINSSTRING(Data[Service],`"demo`"),`"Demo`",`"Other`" )"
}
}
}
}
}
}
}
}
}
}
}
} | ConvertTo-Json

Invoke-RestMethod -Method Post -Uri "https://api.powerbi.com/v1.0/myorg/groups/$($WorkspaceId)/datasets/$($DatasetId)/Default.UpdateDatasources" -Headers $Headers -Body $Body

This script will update the Area calculated column in the Data table of your dataset. You can modify the DAX expression to fit your specific needs.