PowerShell function to run a DAX query against a Power BI Repot / Analysis Services, and accompanying Pester test (which requires a separate .PBIX file that I haven't published yet)

Invoke-DaxQuery.Test.ps1

Describe "Invoke-DaxQuery" {
    Context "Invoke a DAX Query and test the result"{
        # Run queries and compare them
        $results = Invoke-DaxQuery -DAXQuery "EVALUATE ReportCatalogQuery"

        # Test if the config was retrieved
        It "Should verify the count is correct by comparing against a static number" {
            @($results).Count | Should -Be 36
        }
        
        # Test if the first row of results returned
        It "Should verify the first result (row) return matches static value" {
            $results[0].'ReportCatalogQuery[Name]' | Should -Be 'Data Sources'
        }        
    }

}

Invoke-DaxQuery_Against_SQLResults.Test.ps1

$SQLQuery = "; WITH ReportCatalog AS (
    SELECT ItemID -- Unique Identifier
            , [Path] --Path including object name
            , [Name] --Just the objectd name
            , ParentID --The ItemID of the folder in which it resides
            , CASE [Type] --Type, an int which can be converted using this case statement.
                WHEN 1 THEN 'Folder'
                WHEN 2 THEN 'Report'
                WHEN 3 THEN 'File'
                WHEN 4 THEN 'Linked Report'
                WHEN 5 THEN 'Data Source'
                WHEN 6 THEN 'Report Model - Rare'
                WHEN 7 THEN 'Report Part - Rare'
                WHEN 8 THEN 'Shared Data Set - Rare'
                WHEN 9 THEN 'Image'
                WHEN 13 THEN 'PBIX'
                ELSE CAST(Type as varchar(100))
              END AS TypeName
            --, content
            , LinkSourceID --If a linked report then this is the ItemID of the actual report.
            , [Description] --This is the same information as can be found in the GUI
            , [Hidden] --Is the object hidden on the screen or not
            , CreatedBy.UserName CreatedBy
            , CreationDate
            , ModifiedBy.UserName ModifiedBy
            , CTG.ModifiedDate
            , [Type]
    FROM 
      ReportServer.dbo.[Catalog] CTG
        INNER JOIN 
      ReportServer.dbo.Users CreatedBy ON CTG.CreatedByID = CreatedBy.UserID
        INNER JOIN
      ReportServer.dbo.Users ModifiedBy ON CTG.ModifiedByID = ModifiedBy.UserID)
    SELECT b.Name AS [Parent], a.*
      FROM ReportCatalog a
      JOIN ReportCatalog b
        ON a.ParentID = b.ItemID
     WHERE a.[Name] <> 'System Resources' and a.[Path] <> ''
       AND b.[Name] <> 'System Resources' "

Describe "Invoke-DaxQuery" {
    Context "Invoke a DAX Query and test the result"{
        # Run queries and compare them
        $results = Invoke-DaxQuery -DAXQuery "EVALUATE ReportCatalogQuery"

        # Test if the config was retrieved
        It "Should verify the count is correct by comparing against a static number" {
            @($results).Count | Should -Be 36
        }

        # Run query against SQL Server and compare the results to Analysis Services
        $SQLQueryResults = Invoke-Sqlcmd -ServerInstance localhost -Database ReportServer -Query $SQLQuery

        It "Should verify the count is correct by comparing against the results of a SQL Server query" {
            @($results).Count | Should -Be @($SQLQueryResults).Count
        }
        
        # Test if the first row of results returned
        It "Should verify the first result (row) return matches static value" {
            $results[0].'ReportCatalogQuery[Name]' | Should -Be 'Data Sources'
        }        
    }

}    

Query-Dax.ps1

<# This install step only needs to be run once per machine.
    Uncomment it, run it, and then comment it back. #>
##install and load adomdnet if needed
#function InstallAndLoadAdomdNet {
#    "Installing ADOMD.NET"
#    $null = Register-PackageSource -Name nuget.org -Location http://www.nuget.org/api/v2 -Force -Trusted -ProviderName NuGet;
#    $install = Install-Package Microsoft.AnalysisServices.AdomdClient.retail.amd64 -ProviderName NuGet -Force;
#    $dllPath = $install.Payload.Directories[0].Location + "\" + $install.Payload.Directories[0].Name + "\lib\net45\Microsoft.AnalysisServices.AdomdClient.dll";
#    $bytes = [System.IO.File]::ReadAllBytes($dllPath)
#    return [System.Reflection.Assembly]::Load($bytes)
#}
function LoadAdomdNet {
    "Load ADOMD.NET"
    #You need to make sure the amd64.19.12.7 version matches the version you installed in the step above
    $dllPath = "C:\Program Files\PackageManagement\NuGet\Packages\Microsoft.AnalysisServices.AdomdClient.retail.amd64.19.12.7\lib\net45\Microsoft.AnalysisServices.AdomdClient.dll";
    $bytes = [System.IO.File]::ReadAllBytes($dllPath)
    return [System.Reflection.Assembly]::Load($bytes)
}
function Invoke-DaxQuery
{
    [CmdletBinding()]
    param(
    #input the DAX query here that should run against the endpoint
    [Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)]
    [string]
    $DAXQuery = "EVALUATE ReportCatalogQuery",

    #database and server details
    [Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)]
    [string]
    $DatabaseName = "78089090-cbde-4712-bb71-2bdba3df44fc",

    [Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)]
    [string]
    $ServerAddress = "localhost:51222",

    #output location
    [Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)]
    [string]
    $outputFolder = "C:\temp\Power BI\",

    [Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)]
    [string]
    $OutputName = "Customers_Output"

    )
    #compile the final output filepath including the filename
    $outFile = "$OutputFolder\$OutputName.csv"

    #install and load adomdnet if needed
    if ($assembly -eq $null)
    {
        $assembly = LoadAdomdNet;
    }
    
    #create the Analysis Services connection object
    $conn = New-Object -TypeName Microsoft.AnalysisServices.AdomdClient.AdomdConnection;
    $conn.ConnectionString = "Provider=MSOLAP;Initial Catalog=$DatabaseName;Data Source=$ServerAddress;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Update Isolation Level=2"
    $conn.Open();

    #create the AS command
    $cmd = New-Object -TypeName Microsoft.AnalysisServices.AdomdClient.AdomdCommand;
    $cmd.Connection = $conn;
    $cmd.CommandTimeout = 600;
    $cmd.CommandText = $DAXQuery

    #fill a dataset object with the result of the cmd
    $da = new-Object Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter($cmd)
    $ds = new-Object System.Data.DataSet
    $rowCount = $da.Fill($ds)
    
    #close your connection
    $conn.Close();
    $conn = $null;

    #export the result set as a csv
    return @($ds.Tables[0]) 
#    Export-Csv -Path $outFile -NoTypeInformation
}