SELECT * FROM - Code snippets

DimDate - with fiscal and reporting fields (UK), 2010-2030

A date dimension is a crucial component of a data warehouse that contains information related to time. The DimDate table, as the name suggests, is a table that stores date-related information, such as calendar, fiscal, and reporting columns. Using a date dimension table in a data warehouse can make it easier to analyze data over time and make the best use on Power BI's inbuilt DAX time functions. Additionally, having a separate date dimension table can help improve query performance and reduce data duplication in the data warehouse.

Full SQL script here.

Creating multiple Power BI workspaces and uploading reports using PowerShell

Connect-PowerBIServiceAccount

$WSList= "Workspace1, Workspace2, Workspace3"

$WSList= $WSList.split(",");

foreach ($i in $WSList) {

Write-Host "Report $i"

New-PowerBIWorkspace -Name $i

New-PowerBIReport -Path 'C:\Reports\Test Report.pbix' -Name "Report $i" -Workspace ( Get-PowerBIWorkspace -Name $i )

}

DAX measure for bugs: passed/untested/failed colour traffic light measure

My table Bugs has a column status that shows the current status of each bug. We wanted a traffic light system in Power BI that displays whether all bugs have passed (green), are untested (amber) or have failed (red). And yes emojis can be used in DAX (all UNICODE charcaters!)

BugsPassColour =

IF (

CALCULATE (

COUNT ( 'Bugs'[Status] ),

FILTER ( 'Bugs', 'Bugs'[Status] = "Fail" )

) > 0,

"🔴",

IF (

CALCULATE (

COUNT ( 'Bugs'[Status] ),

FILTER ( 'Bugs', 'Bugs'[Status] = "Untested" )

) > 0,

"🟡",

"🟢"

)

Azure deployment pipelines for Power BI reports

The build pipeline for a PBI reports project is very simple. We just want to copy the pbix files.

trigger:

- main

pool: default

steps:

- task: CopyFiles@2

inputs:

SourceFolder: '$(Build.SourcesDirectory)'

Contents: '**.pbix'

TargetFolder: '$(Build.ArtifactStagingDirectory)'

- task: PublishBuildArtifacts@1

inputs:

PathtoPublish: '$(Build.ArtifactStagingDirectory)'

ArtifactName: 'drop'

publishLocation: 'Container'

The release will then grab the report files and publish the reports using a Power BI actions task.

steps:

- task: maikvandergaag.maikvandergaag-power-bi-actions.PowerBIActions.PowerBIActions@5

displayName: 'Power BI Action: Publish'

inputs:

PowerBIServiceEndpoint: 'pbi service connection'

WorkspaceName: @WorkspaceName

PowerBIPath: '$(System.DefaultWorkingDirectory)/[repo]/drop/*.pbix'