In this blog post I am going to show you how to use PowerShell to run a DAX query from my dataset, and then store the results in a CSV file.

I will also include the PowerShell code!

I really liked the awesome blog post by Kay on the Power BI Team which you can find here: Announcing the public preview of Power BI REST API support for DAX Queries

Before I start with the PowerShell code I had completed the following requirements

  • Allow XMLA endpoints and Analyze in Excel with on-premises datasets
  • The required users were either Admins or Members of the App Workspace. Or the user had build permissions on the underlying dataset.
  • The App Workspace was using the Modern or V2 of the App workspaces.

In my example below I am going to be using a Service Principal to get the data, this allows me to be able to run the PowerShell code with some automation.
NOTE: This also means that underlying user in my Service Principal group must have a PPU license to be able to read the data.

Finding the dataset GUID

One of the requirements for the PowerShell script is that I need to put in the dataset GUID, in the steps below I show you how to find this.

My dataset is linked to my Power BI Report. I go into the Power BI App Workspace where my dataset is stored.

NOTE: If you are using Power BI reports that use a dataset from another App Workspace you have to go and find which dataset that workspace lives in. You can do this by right clicking on your report and selecting View Lineage

Now to get the dataset GUID, I go into my App Workspace, right click on my dataset and click on settings as shown below.

What I then need to do is to look in the URL and make a note of the GUID after /datasets/ as highlighted below in RED

In my example above my dataset GUID is: 0f2fc89a-cc2a-490a-9bc9-99e5900e77a7

Getting the DAX Query

The first that I needed to do was to get the DAX query for the data that I wanted to extract into my CSV file.

I like to do this using Power BI Desktop and using the Performance Analyzer, to me this is a little hack that makes it quick and easy for me to get the DAX syntax without having to write it myself!

I went into my PBIX report, clicked on Performance Analyzer

I already had my table ready, I clicked on Start Recording and then refreshed the visual, which then gave me the option to copy the query as shown below.

This is what my DAX query looked like below

// DAX Query
DEFINE
VAR __DS0Core =
SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL('Date'[Date], "IsGrandTotalRowTotal"),
      "Sales_Amount", 'Sales'[Sales Amount],
"v_Sales_Amount_FormatString", IGNORE('Sales'[_Sales Amount FormatString])
    )

VAR __DS0PrimaryWindowed =
TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'Date'[Date], 1)

EVALUATE
  __DS0PrimaryWindowed

Now that I have my DAX query onto the next steps to run this in PowerShell

Running the DAX query using PowerShell

Below is the script that I used to run the DAX query, and I comment all the details of what I needed to do to get this working in the script.

# Reference: https://powerbi.microsoft.com/en-ca/blog/announcing-the-public-preview-of-power-bi-rest-api-support-for-dax-queries/

# I have found using PowerShell on my PC this is needed to work.
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

# User Account Details
# This could be a user account or a service account
$username = "fourmooisawesome@fourmoo.com"
$password = "IsThisReallyMyPassword!" | ConvertTo-SecureString -asPlainText -Force 
$credential = New-Object System.Management.Automation.PSCredential($username, $password)

# Logging in with above credentials
Connect-PowerBIServiceAccount -Credential $credential

# Getting the DAX Query URL to pass through
# I had to get the dataset GUID from Settings -> Datasets in the Power BI Service

$requestUrl = "datasets/0f2fc89a-cc2a-490a-9bc9-99e5900e77a7/executeQueries"

# DAX Query - This is what I got from using Power BI Desktop
# NOTE: The double quote is a reserved character and it has to be escaped with a backslash.
# EG: If this is "My Text" it needs to be written as \"My Text\"
$requestBody = @"
{
    "queries":
        [
            {"query": "DEFINE
                          VAR __DS0Core = 
                            SUMMARIZECOLUMNS(
                              ROLLUPADDISSUBTOTAL('Date'[Date], \"IsGrandTotalRowTotal\"),
                              \"Sales_Amount\", 'Sales'[Sales Amount])
                            

                          VAR __DS0PrimaryWindowed = 
                            TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'Date'[Date], 1)

                        EVALUATE
                          __DS0PrimaryWindowed
                        "
 

            }
        ],
        "serializerSettings": {"includeNulls": false}
}
"@ 

# Creating the Result Variable by passing through the DAX Query to the Power BI PowerShell Module
$result = Invoke-PowerBIRestMethod -Method POST -Url $requestUrl -Body $requestBody

# I take the results and convert it from JSON
$parsed = $result | ConvertFrom-Json

# This is where I take the results and export it to CSV 
$parsed.results[0].tables[0].rows | Export-Csv D:\Myresults2.csv

I then went to my D: Drive and opened the file, which was shown below has got the results in a CSV format

Summary

In this blog post I have shown how you can use the Power BI PowerShell module to run a DAX query and then store the results in a CSV file.

If there are any comments or questions, please let me know.

Thanks for reading and have an awesome day!