DAX and Power Query Marcus Radue 101 January

DAX and Power Query Marcus Radue 101 January 2021

Introduction • Senior Data Analytics Engineer at Skyline Technologies • Over 5 years experience in Data Analytics • • • Microsoft SQL Stack Power Platform Azure stack Follow me on social media Linked. In: https: //www. linkedin. com/in/marcusradue-0 ab 5496 a/ Twitter: @Marcus_Radue

Agenda • • • DAX vs Power. Query in Power BI Getting started with Power. Query tips and best practices Getting started with DAX tips and best practices Q&A

Where is DAX and Power Query used in Power BI? 1 2 • Ingestion tool for Power BI (also used in SSAS 2019 or newer and Azure Analysis Services) • Vertipaq engine • Dataflows • Power Query is used in other Azure tools • Power Platform dataflows • Azure Data Factory (wrangling dataflows) • Synapse dataflows Power BI/Tabular Order of Operations in Processing 1. Storage engine - Power Query 2. Formula engine - DAX • DAX – Data Analytical Expressions • Create calculations within your Power BI dataset • Tables • Columns • Measures

Power Query Intro • UI overview • Oftentimes there are multiple ways to do the same function • Advanced editor • • Date value extraction Conditional/Custom columns Merge Parameters

Power Query best practices • Remove unused columns • Filter dataset • Split high cardinality columns if applicable • Group similar steps • Query folding • Relational database sources • Use comments

Power Query Demo

DAX Intro • Evaluation context • Iterator functions • Filter context • “All” clause • Variables • Calculate • Time Intelligence

DAX best practices • Naming convention • • Qualified column names Do not qualify measures Use folders for organization in your model Use variables Avoid Bi-directional filters/relationships Avoid complex filtering Use third-party tools to fine tune your DAX expressions • • • DAX Studio Tabular Editor Vertipaq Analyzer

DAX Demo

Questions