POWERBI BUILDING BLOCKS WOW GALWAY POWERBI MEETUP GROUP

POWERBI BUILDING BLOCKS – WOW! GALWAY POWERBI MEETUP GROUP

BIT ABOUT ME • Anne Walsh – freelance trainer AKA The Excel Lady • Based in Galway • Author of “Your Excel survival kit – your guide to surviving and thriving in an Excel world” • www. the-excel-expert. com and www. theexcellady. com

WHAT ARE THE BUILDING BLOCKS? q Tables q Relationships between tables q Formulas – Calculated Columns and Measures

WHAT’S A TABLE?

TABLES View in Power. BI IN DATA TERMS. . • Set of data about one thing (Entity) • Also “table” format in Excel • No blank rows/columns • Headings in single cell • Like with like • Has a primary key This Photo by Unknown Author is licensed under CC BY-SA

TABLES LOOKUP (DIM) TABLES DATA/FACT TABLES • Lookup/Dim (Dimension) tables – more information about the entity e. g. customers, products, territories • Granular e. g. returns, sales – Primary key – unique identifier – other details about the entity • Foreign key – Same as primary key in Lookup table – Duplicated

SO WHAT? • In Power. BI – often have to pull data from different sources • Have to have a relationship between tables if you want to pull from multiple data sources – One (Primary key) to many (Foreign key)

RELATIONSHIPS? One to Many…

VIDEO TO DEMO RELATIONSHIPS • Main types of relationships – One to One/One to Many • View longer introduction here - Curbal – https: //www. youtube. com/watch? v=-4 yb. WQSRc. OY – Short introduction – https: //www. youtube. com/watch? v=f. VW 4 MCr 0 APA

MEASURES AND CALCULATED COLUMNS How to get numbers from your data set

CALCULATED COLUMNS AND MEASURES IN POWERBI CALCULATED COLUMN MEASURES • Calculated in the table as an additional field • Done more in the visual side of things • DAX – similar syntax to Excel • Give it a name – it can be re-used in other measures. • Usually linked to a table

WHEN TO USE CALCULATED COLUMNS VS MEASURES USE CALCULATED COLUMNS USE MEASURES WHEN. . • Need row context e. g. extract day from a date • Doing aggregate calculations e. g. sum/average of a field • Once it’s created – all done so heavier load on system • Schrodinger’s cat – only happens when it’s loaded • Do you need to filter on this? Use a calculated column • Need to use in another calculation

CHOICES, CHOICES • Article on what to use when - https: //powerpivotpro. com/2013/02/when-to-usemeasures-vs-calc-columns/ https: //community. powerbi. com/t 5/Desktop/column-vs-measure/td-p/13201 https: //projectbotticelli. com/knowledge/dax-calculated-columns-vs-measures-videotutorial

CALCULATED COLUMN • https: //www. youtube. com/watch? v=62 m. Lfi. Ncq. VM

FORMULAS (MEASURES) • Implicit • Explicit Drag and drop Values field Create from scratch Can’t use again Build linked to table Can’t use in other measures Can be re-used in other measures

CREATING A MEASURE • https: //www. youtube. com/watch? v=r. CXg. V__3 ddw

VIDEO – IMPLICIT VS EXPLICIT • https: //www. youtube. com/watch? v=S 03 h. Cs. Jnr. Qg

CONCLUSION • Tables – store your data – Primary key and foreign keys • Relationships – nothing happening until you create a relationship • Calculated columns and measures – Doing something with your data