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

[mm456]

Hi all,

I'm trying to automate a PowerBi report that I have published that uses parameters to change the values for data coming from AzureBlobs.  I have one report and I'm using powershell to deploy changes to various workspaces such as changing the title of the report. However, I have a calculated called Area (below) that checks for specific strings in another column and renames them.

Area = SWITCH (

    TRUE(),

    CONTAINSSTRING(Data[Service],"Dev"),"Test",

    CONTAINSSTRING(Data[Service],"demo"),"Demo",

     "Other"

    )

Since, I'm using multiple datasources and expect this column to change from time to time with different values thus the calculation must be done on the fly. How can I calcucate this field either using PowerShell or within PowerBI or any other suggestions?

Thanks,

Marl

[Adamboer]

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.UpdateDatas..." -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.

[mm456]

Hi @Adamboer,

I'm getting the error below when running the above query;

Before the error, using the Microsoft API test https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/update-datasources#code-try-0 threw a "connection details" error which I added into the above code using the format


"connectionDetails": {
"account": "AzureBlobAccount",
"domain": "blob.core.windows.net"},

On further reading the Update Datasources documentation it seems that Azure Blob Storage are not supported under "Limitations". Would this be the case or I'm missing something?

[v-yueyunzh-msft]

Hi , @mm456 

Depending on your requirements, it seems that you have some dynamic calculated columns, you need to deploy changes to various workspaces such as changing the title of the report, if convenient, you can try the "deployment pipelines" feature that comes with Power BI:

Get started using deployment pipelines, the Power BI Application lifecycle management (ALM) tool - P...

According to my research, it can also support editing the deployment rule when deploying, and modifying the corresponding data source of the data set after the deploy:
Create deployment rules Power BI Application lifecycle management (ALM) - Power BI | Microsoft Learn

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly