PowerShell for DAX: Filters applied
When there are a lot of filter objects on screen, especially when the UI design is not the cleanest, it can be difficult for users to understand at a glance which filters have been applied, and which values they are taking on.
I had wanted to have a "diagnostic" text box that showed exactly that: which filters were being put on, and which values they were applying. I did this by having a HTML text box display a measure, which generated some HTML.
I did this manually for a few filters, but the scope of the page grew so much that there are too many filters to take care of manually. So, I wrote this PowerShell script to make sure that I can generate the DAX that generates the HTML all at once.
# This script generates DAX code for the measure HTML\Filters Display in the main data model
# The purpose is to show which filters are being applied on screen, and what values are being taken on
using namespace System.Text; #for StringBuilder
$filters = @(
# main filters
@{displayName = 'Hubs'; variable = 'hubs'; table = 'refServiceLines'; column = 'Hub'}
@{displayName = 'Service Lines'; variable = 'servicelines'; table = 'refServiceLines'; column = 'Service Line Original'}
@{displayName = 'Hubs (projects)'; variable = 'hubsprojects'; table = 'refServiceLinesProjects'; column = 'Hub'}
@{displayName = 'Service Lines (projects)'; variable = 'servicelinesprojects'; table = 'refServiceLinesProjects'; column = 'Service Line Original'}
@{displayName = 'Disciplines'; variable = 'discipline'; table = 'disciplines'; column = 'Discipline'}
# @{displayName = 'Names'; variable = 'peoplename'; table = 'people'; column = 'Name'}
@{displayName = 'People'; variable = 'employeeNumber'; table = 'timecards'; column = 'Employee Number'}
@{displayName = 'Manager'; variable = 'manager'; table = 'reportingHierarchy'; column = 'Management Chain'}
@{displayName = 'Dates'; variable = 'dates'; table = 'projectPeriods'; column = 'Date Week Ending'}
@{displayName = 'Countries'; variable = 'countries'; table = 'peopleLocation'; column = 'Country'}
@{displayName = 'Cities'; variable = 'cities'; table = 'peopleLocation'; column = 'City'}
# Things that need to have something on them as a default
@{displayName = 'Employment Status'; variable = 'employmentStatus'; table = 'people'; column = 'Employment Status'}
@{displayName = 'Time period'; variable = 'period'; table = "'Project Periods Clamp'"; column = 'Period'}
# @{displayName = 'Financial Years'; variable = 'finyear'; table = 'projectPeriods'; column = 'Financial Year'}
@{displayName = 'Grades'; variable = 'grade'; table = 'people'; column = 'Grade'}
@{displayName = 'Hours Types'; variable = 'hourstypes'; table = 'timecards'; column = 'Hours Type'}
@{displayName = 'Project Name and Numbers'; variable = 'projectnamenumber'; table = 'timecardsProjects'; column = 'Project Name and Number'}
@{displayName = 'Project Managers'; variable = 'pms'; table = 'timecardsProjects'; column = 'Current Project Manager'}
@{displayName = 'Chargeability Basis'; variable = 'chargeabilityBasis'; table = "'Chargeability Denominator'"; column = 'Basis'}
@{displayName = 'Global Projects Hours Excluded'; variable = 'globalprojects'; table = 'timecardsProjects'; column = 'Exclude Global Project (text)'}
@{displayName = 'International Projects Hours'; variable = 'international'; table = 'timecardsProjects'; column = 'International'}
)
function makeDax ([string] $variable, [string] $table, [string] $column) {
if ($variable -eq 'employeeNumber') {
return @"
VAR $variable =
VAR distinctElements = DISTINCT($table[$column])
VAR countOfElements = COUNTROWS(distinctElements)
VAR filtered = CONCATENATEX(distinctElements, [$column], delimiter)
VAR everything = CALCULATE(CONCATENATEX(DISTINCT($table[$column]), [$column], delimiter), ALL($table))
RETURN IF(
filtered = everything,
"All",
countOfElements & " selected"
)
"@
} elseif ($variable -in @('peoplename', 'discipline', 'dates', 'projectnamenumber', 'pms')) {
return @"
VAR $variable =
VAR distinctElements = DISTINCT($table[$column])
VAR countOfElements = COUNTROWS(distinctElements)
VAR filtered = CONCATENATEX(distinctElements, [$column], delimiter)
VAR everything = CALCULATE(CONCATENATEX(DISTINCT($table[$column]), [$column], delimiter), ALL($table))
RETURN IF(
filtered = everything,
"All",
IF(
countOfElements > 5,
countOfElements & " selected",
filtered
)
)
"@
} else {
return @"
VAR $variable =
VAR filtered = CONCATENATEX(DISTINCT($table[$column]), [$column], delimiter)
VAR everything = CALCULATE(CONCATENATEX(DISTINCT($table[$column]), [$column], delimiter), ALL($table))
RETURN IF(filtered = everything, "All", filtered)
"@
}
}
function makeHtmlTableRow ([string] $displayName, [string] $variable) {
switch ($variable) {
'employmentStatus' {
return @"
& IF(
$variable = "All",
"<tr><td style='vertical-align: top;'>$displayName :</td><td style='color: #F43A4F'>All, including no longer employed</td></tr>",
"<tr><td style='vertical-align: top;'>$displayName :</td><td>" & $variable & "</td></tr>"
)
"@
}
'finyear' {
return @"
& IF(
$variable = "All",
"<tr><td style='vertical-align: top;'>$displayName :</td><td style='color: #F43A4F'>All, including past years</td></tr>",
"<tr><td style='vertical-align: top;'>$displayName :</td><td>" & $variable & "</td></tr>"
)
"@
}
'period' {
return @"
& IF(
$variable = "All",
"<tr><td style='vertical-align: top;'>$displayName :</td><td style='color: #F43A4F'>No time period selected</td></tr>",
"<tr><td style='vertical-align: top;'>$displayName :</td><td>" & $variable & "</td></tr>"
)
"@
}
default {
return @"
& IF( $variable <> "All", "<tr><td style='vertical-align: top;'>$displayName :</td><td>" & $variable & "</td></tr>" )
"@
}
}
}
[PSCustomObject] $filters | Export-Csv '.\filters.csv'
# check that all variables are unique
$duplicates = ($filters.variable | group | ? { $_.Count -gt 1 })
$duplicates
$allVariablesUnique = $duplicates.count -eq 0
function out {
$dax = [StringBuilder]::new()
$htmlTable = [StringBuilder]::new()
[void] $htmlTable.AppendLine("`"<div style='font-size: x-small;'>`"")
[void] $htmlTable.AppendLine("& `"<b>Filters applied:</b><br>`"")
[void] $htmlTable.AppendLine("& `"<table>`"")
foreach ($_ in $filters) {
[void] $dax.AppendLine($(makeDax -variable $_.variable -table $_.table -column $_.column))
[void] $htmlTable.AppendLine($(makeHtmlTableRow -displayName $_.displayName -variable $_.variable))
}
$out | Set-Content filters.txt
[void] $htmlTable.AppendLine("& `"</table>`"")
[void] $htmlTable.AppendLine("& `"</div>`"")
[void] $htmlTable.ToString()
[void] $dax.ToString()
$out = @"
Filters Display =
VAR delimiter = "; "
$dax
RETURN
$htmlTable
"@
# $out | scb
$out > '.\full.txt'
}
if ($allVariablesUnique) {
out
} else {
Write-Host -ForegroundColor Red 'Variable names are not unique'
}