Power BI Advanced Concepts

REACH Webinar Series 2026 — Session Three

// Robert Pilgrim, Ph.D.

↓

Who am I

Robert Pilgrim, Ph.D. | rpilgrim@uark.edu
Associate Director of Data Strategy & Insights
Office of Sponsored Programs, UARK
B.Sc Astrophysics | Ph.D. Space Science
rpilgrim.com | LinkedIn | Substack

Session Framework

All data / dashboards are dummy
Prior Sessions:
- Session 01 — Power BI Overview
- Session 02 — Security and Teams

Session framework overview

Recap: Publish Report

Power BI Desktop → click “Publish”
Select your Workspace

Power BI publish screenshot

Simplified Reporting Groups Via App

Reporting groups via app

Recap: Deploying an App from A Workspace

Power BI PBIX vs PBIP format
GitHub Integration
Power BI and GitHub: Better Together for Higher Ed Analytics
Managing Complex Analytics Workflows in GitHub

Deploying an App from a Workspace

PBIX vs PBIP format

DAX 101

DAX is an expression language NOT a programming language
At its core DAX allows us to override the default behaviors of pivot tables
Quick Start
- Counts and Amounts

Recap Data Model

Example data model

Recap DAX

DAX recap

New Concept Leveraging DAX

How do we actually build stuff?
Think Pivot tables in Excel
Next let’s actually see how this works

DAX concept

Counts (Non Data Model)

What are the counts of proposals at the Campus, College and Department level?
How could we program this in R, SAS, Python, SQL etc.?
- Q1 Filter data for Campus > Count the number of distinct proposal numbers
- Q2 Filter data for College > Count the number of distinct proposal numbers
- Q3 Filter data for Department > Count the number of distinct proposal numbers

Advanced Submission Counts

Just Tenure Track Faculty
Submitted to NSF
Top 10 faculty NSF submissions within each college
Rates for TT faculty who received start-up funding
Conceptually these things are not hard just apply the filters and do the calculations

Leveraging Our Data Model

Let’s make some calculations
Official name: DAX measures

DAX measures concept

Count Investigators

Count Investigators DAX

Count Sponsors

Count Sponsors DAX

Proposal Amount

Proposal Amount DAX

Simple Tables

[Proposal Amount] by Fiscal Year and College
Just like a pivot table in Excel

Simple tables example

Introducing CALCULATE

Funding Rates by College
You could code this by making each part into a table
Merge, then do the actual calculation

CALCULATE example

Proposals Not-Funded

CALCULATE filters the base calculation before it is calculated
We want to count proposals but only those that are “Not-Funded”
i.e. FILTER the data first, then do the calculation

Proposals Not-Funded DAX

DAX Breakdown Not-Funded

DAX breakdown Not-Funded

DAX Breakdown Funded

Filter my data for “Funded” first
Then count

DAX breakdown Funded

Funding Rate

Funded / (Funded + Not-Funded)

Funding Rate DAX

Funding Rate Result

Funding rate result table

What is the Point?

All this talk but we still have not built anything!!
Next slide will bring this home (hopefully!)

DAX concept revisited

Building

Building a report

User-Defined Functions (UDFs)

What is a UDF?
A reusable DAX expression you write once and call anywhere
Same idea as a function in Excel or Python
Solves the copy-paste DAX problem

UDF overview

UDF Example

YoY Change

UDF YoY Change example

UDF Reusable

UDF reusable pattern

Activate UDF’s

Activate UDFs in Power BI

R and Python in Power BI

Power BI supports R and Python natively
Two places they plug in: Power Query and Visuals
You already have a license if you have Power BI Desktop

R and Python in Power BI

R and Python details

Show me the Code

Pass your data into a data frame

Python code in Power BI

R/Python Visual Output

R/Python visual output

External Tools

Power BI Desktop supports external tools via the ribbon
Free, open-source, actively maintained
Change how you work — not just what you can do

External tools ribbon

DAX Studio

Write and test DAX outside the report
Free query editor for DAX
Enhance performance, find slow measures
Export data from the model to CSV

DAX Studio screenshot

Tabular Editor

Free / paid data model editor
Manage measures, tables, and relationships in bulk
Flags model problems automatically
C# scripting for DAX automation

Tabular Editor screenshot

The End

rpilgrim.com | LinkedIn | Substack

We have focused on the frameworks and workflows

Contact info

Closing image