PowerShell for Power BI: Reconstructing a measure
When debugging data errors in Power BI, there is a lot of tracing involved. I need to look at a measure in a visual, and trace back all of its references to the base table columns. Often, measures refer to other measures, which in turn refer to other measures, and so on. It can be easy to lose track.
This PowerShell function "reconstructs" a measure down to its base table column references. It picks up on all of the measure references, substitutes them for their definitions, and keeps going until there are no more measures to substitute. This way, the entire context of this measure will be visible in one page.
It needs the Power BI file to be saved as a .pbip file (not a .pbix). This way, the model.bim file (which has all the measure definitions) will be available for access.
The function takes two arguments. The first is the location of the .pbip file, and the second is the name of the target measure to be reconstructed.
The output will be messy so will have to be formatted manually. One day, I may look into integrating this Dax Formatter API by SQLBI.
Example
As a example, let's say that these are the measure we are looking at:
Measure Top =
[Measure Middle A] + [Measure Middle B]
Measure Middle A =
CALCULATE(
[Measure Bottom C],
TableX[Department] = "This Department"
)
Measure Middle B =
CALCULATE(
[Measure Bottom C],
TableX[Department] = "That Department"
)
Measure Bottom C =
SUM(TableY[Revenue])
The final result, when reconstructing Measure Top, will be:
CALCULATE(
SUM(TableY[Revenue]),
TableX[Department] = "This Department"
) + CALCULATE(
SUM(TableY[Revenue]),
TableX[Department] = "That Department"
)
It becomes clear the ultimately, Measure Top is derived from the Revenue column of TableY, and it's being filtered on the Department column of TableX.
Full code
function reconstructDaxMeasure (
[Parameter(Mandatory)]
[string] $path,
[Parameter(Mandatory)]
[string] $measureName
) {
# this is the shell function
# there are some setup steps in there, and an ending step
# recursion is done in "reconstruct"
# DEFINE FUNCTIONS
function createDictionary ($path) {
# set up variable for storing results
$out = [System.Collections.Generic.List[PSCustomObject]]::new()
$projectName = $path.split('\')[-1]
# $modelFile is the path to the model.bim file
$modelFile = "$path\$projectName.SemanticModel\model.bim"
# put in argument -AshHashTable because some property names are empty strings (e.g. "": { ... })
$model = Get-Content $modelFile | ConvertFrom-Json -AsHashtable
# for all tables that have measures...
$model.model.tables |
Where-Object { $null -ne $_.measures } |
ForEach-Object {
# ... for every measure in there...
$_.measures |
ForEach-Object {
# ... collect the name and expression
$out.Add(
[PSCustomObject] @{
name = $_.name.Trim();
expression = $_.expression -join ''
}
)
}
}
return $out
}
function getMeasureDefn ($measureName, $dict) {
return ($dict | Where-Object { $_.name -eq $measureName }).expression
}
function reconstruct ([string] $original, [PSCustomObject] $dict) {
# "get everything inside square brackets", to be used in Select-String
$regex = "(?<=\[).*?(?=\])"
# pick up on all the "measures" (all the things in square brackets)
$measures = ($original | Select-String $regex -AllMatches).Matches.Value
# for every measure reference that's detected...
$measures |
ForEach-Object {
# ... if it's in the dictionary...
if ($dict.name -contains $_) {
# ... get the definition...
$measureDefn = getMeasureDefn $_ $dict
# ... and substitute the measure reference with its definition
$original = $original.replace("[$_]", $measureDefn)
}
}
# pick up on all the measures, _again_, after replacing them all with their definitions
$measures = ($original | Select-String $regex -AllMatches).Matches.Value
# for all of the measures now, check if there are more that have definitions in the dictionary
$truefalse = $measures | ForEach-Object { $_ -in $dict.name }
# if there are more measures to pick up, go again (recurse)...
if ($truefalse -contains $true) {
reconstruct $original $dict
}
else {
# ... otherwise, return the final reconstructed measure
return $original
}
}
# RUN ROUTINE
# get all of the mesaures in the model and all of the defintions
$dict = createDictionary $path
# get the definition of the target measure
$original = getMeasureDefn $measureName $dict
# call recursive function to get definitions all the way down
$result = reconstruct $original $dict
# return result
return $result
}