International Association Crime Analysts
December 20, 2023
POWER BI:
INTRODUCTION TO DAX
Svetlana Gubin
Business Analyst II, Oakland PD
TABLE OF CONTENTS
Introduction to the DAX
✓ Introduction
✓ Simple Formulas
✓ Examples of Advanced Formulas
Additional Information
Conclusion and QA
TERMINOLOGY
Data Analysis Expressions (DAX) is a formula language
used in Microsoft Power BI to perform calculations and
create custom measures, columns, and tables.
DAX allows you to manipulate data within Power BI to
generate valuable insights and reports.
SOME KEY CONCEPTS
1. Measures Quick and Custom:
Measures are calculations that aggregate data in a virtual table.
2. Calculated Columns:
Calculated columns are another way to extend the data model.
3. Time Intelligence:
DAX includes powerful time intelligence functions, allowing you to
perform dates and time period calculations.
DAX AND EXCEL
= (A1*A2)-B2
Excel: work on cells
Count of Incidents = COUNT(Crimes[RN Number])
DAX: works only on tables
Count of Incidents - a name for a measure or calculation
Crimes – table name (if a name has two and more words with
space, use parenthesis, ‘Crime incidents’)
[RN Number] – column name
DAX AND EXCEL SIMILARITY
= IF([@[Event Time] > 19, “Night”, “Day”)
Excel:
Evening Incidents = IF(Crimes[Event Time] > 19, “Night”, “Day”)
DAX:
POWER BI – GET DATA
POWER BI – TEXT/CSV
POWER BI – DATA
POWER BI – LOAD DATA
TIME INTELLIGENCE OPTIONS
QUICK MEASURE
QUICK MEASURE
QUICK MEASURE
QUICK MEASURE
QUICK MEASURE
QUICK MEASURE
NEW TABLE
Calendar = CALENDAR ("01-01-2018", "12-31-2023")
NEW TABLE
MANAGE RELATIONSHIP
MANAGE RELATIONSHIP
MANAGE RELATIONSHIP
CHANGE DATE TYPE
CALCULATED COLUMNS
Year = YEAR ('Calendar'[Date])
MonthNumber = MONTH ('Calendar' [Date])
MonthPrefix = FORMAT ('Calendar' [Date], "MMM")
DateWithCrimes = 'Calendar'[Date]<=MAX('CrimeWatch'[Date])
TIME INTELLIGENCE
Count = Count('CrimeWatch'[CaseNumber])
YTDCount = TOTALYTD([Count],'Calendar'[Date])
MTDCount = TOTALMTD([Count],'Calendar'[Date])
TIME INTELLIGENCE
PYTDCount = CALCULATE([YTDCount],
CALCULATETABLE(DATEADD('Calendar'[Date], -1, YEAR),
'Calendar'[DateWithCrimes] = TRUE))
TIME INTELLIGENCE
YEAR
YOYTDCount = IF(NOT ISBLANK([YTDCount]) && NOT
ISBLANK([PYTDCount]), [YTDCount] - [PYTDCount])
YOYTD% = IF([PYTDCount] = 0, "NC",
DIVIDE([YOYTDCount], [PYTDCount]))
MONTH
MOMTDCount = IF(NOT ISBLANK([MTDCount]) && NOT
ISBLANK([PMTDCount]), [MTDCount] - [PMTDCount])
MOMTD% = IF([PMTDCount] = 0, "NC",
DIVIDE([MOMTDCount], [PMTDCount]))
POWER BI NEW FILTER
POWER BI PREVIEW FEATURES
POWER BI QUICK MEASURE SUGGESTIONS
POWER QUICK MEASURE SUGGESTIONS - TABLE
POWER QUICK MEASURE SUGGESTIONS - MEASURE
ADDITIONAL INFORMATION (BOOKS)
✓ Entry Level
✓ Beginning DAX with Power BI (2018) by Phillip Seamark
✓ The Definitive Guide to DAX: Business Intelligence for Microsoft Power BI,
SQL Server Analysis Services, and Excel (2019) by Marco Russo and Alberto
Ferrari
✓ Advance Level
✓ DAX Cookbook (2020) by Greg Deckler
✓ Pro DAX and Data Modeling on Power BI (2023) by Adam Aspin
✓ Extreme DAX (2022) by Michiel Rozema and Henk Vlootman
✓ Time Intelligence
✓ DAX Patterns (2020) by Marco Russo and Alberto Ferrari
https://www.daxpatterns.com/patterns/
ADDITIONAL INFORMATION (LINKS)
✓ Websites, Blogs, and YouTube channels
✓ Power Query documentation
https://docs.microsoft.com/en-us/power-query
✓ Power BI documentation
https://docs.microsoft.com/en-us/power-bi/
✓ Power BI Tips
https://powerbi.tips/
✓ RADACAD
https://radacad.com/
✓ SQLBI
https://www.sqlbi.com/
✓ Guy in a Cube
https://guyinacube.com/
✓ Curbal
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw
