John Tran Business Program Manager The Suddath Companies

  • Slides: 26
Download presentation
John Tran Business Program Manager, The Suddath Companies Power User Group Admin, Power BI

John Tran Business Program Manager, The Suddath Companies Power User Group Admin, Power BI

Thank you to our sponsors 1. NR Computer Learning Center 2. Agile Alliance Solutions

Thank you to our sponsors 1. NR Computer Learning Center 2. Agile Alliance Solutions

“Baking a Cake” Load to Data Model Connect to Data What’s on Sale: -

“Baking a Cake” Load to Data Model Connect to Data What’s on Sale: - ERP - Oracle - SQL Server - Excel Data Warehouse “Baking Pan” ETL “Blender” Data Sources “Raw Ingredients” -Filter incoming data - Combine data sources to create a single set -Create custom columns -Create relationships between data -Create reports

Feature Extract/Load your data

Feature Extract/Load your data

Database 101: Data/Relationships Feature • Fact Table • A transactional table is the most

Database 101: Data/Relationships Feature • Fact Table • A transactional table is the most basic and fundamental • Dimension Table • A collection of reference information about a measurable event • Joins/Relations • Instruction to combine data from two sets of data • Dimensional Modelling • Star Schema • Snowflake Schema

Database 101: Data/Relationships Feature

Database 101: Data/Relationships Feature

Feature Clean your data

Feature Clean your data

Query Editor – Normalize the Data Feature • Duplicate Table • Reference Table •

Query Editor – Normalize the Data Feature • Duplicate Table • Reference Table • Transform • Data Type, Rename, Replace • Transpose Data • Group By • Add Column • Conditional Column • Format

Query Editor Feature

Query Editor Feature

Feature Analyze/Transform your data

Feature Analyze/Transform your data

Measure and Columns – Transform the Data Feature • Data Analysis Expressions (DAX) is

Measure and Columns – Transform the Data Feature • Data Analysis Expressions (DAX) is a library of functions and operators that can be combined to build formulas and expressions in Microsoft SQL Server Analysis Services, Power Pivot in Excel, and Power BI Desktop. • • Date and Time Functions Time Intelligence Functions Filter Functions Information Functions Logical Functions Text Functions Math and Trig Functions Etc. Create your own measures in Power BI Desktop

Measure and Columns Feature

Measure and Columns Feature

Measure and Columns - “Implicit” Versus “Explicit” Feature • “Implicit” Versus “Explicit” Measures •

Measure and Columns - “Implicit” Versus “Explicit” Feature • “Implicit” Versus “Explicit” Measures • Explicit Measure – a measure you create by writing a formula in the Editor • Implicit Measure – what you get when you just check a numeric column’s checkbox • My opinion • Never, ever create implicit measure • Impacted calculations - Need to only change a single formula to fix everything • Never perform spreadsheet/data surgery again Create your own measures in Power BI Desktop

Feature Sample of Explicit Measure/Column

Feature Sample of Explicit Measure/Column

Measure and Columns – “Think like DAX” Feature • It is important for you

Measure and Columns – “Think like DAX” Feature • It is important for you to get comfortable thinking about measure the way DAX engine thinks about them • Example: You filter the table X to just the rows where Priority=High and Year=2017, then sum up the Sales. Amt column over those remaining row Create your own measures in Power BI Desktop

Feature Let’s learn DAX

Feature Let’s learn DAX

Data Analysis Expressions (DAX) Feature • Data Analysis Expressions (DAX) is a library of

Data Analysis Expressions (DAX) Feature • Data Analysis Expressions (DAX) is a library of functions and operators that can be combined to build formulas and expressions in Microsoft SQL Server Analysis Services, Power Pivot in Excel, and Power BI Desktop. • • Date and Time Functions Time Intelligence Functions Filter Functions Information Functions Logical Functions Text Functions Math and Trig Functions Etc.

Date and Time Functions Feature • These functions in DAX are similar to date

Date and Time Functions Feature • These functions in DAX are similar to date and time functions in Microsoft Excel. However, DAX functions are based on the datetime data types used by Microsoft SQL Server. • MONTH ( ) • YEAR( ) • WEEKNUM ( ) • NOW ( ) vs TODAY ( ) • DATEDIFF ( ) • CALENDAR ( )

Time Intelligence Functions Feature • These functions help you create calculations that use built-in

Time Intelligence Functions Feature • These functions help you create calculations that use built-in knowledge about calendars and dates. By using time and date ranges in combination with aggregations or calculations, you can build meaningful comparisons across comparable time periods for sales, inventory, and so on. • TOTALYTD ( ) • PREVIOUSYEAR ( ) • MORE…

Filter Functions Feature • These functions help you return specific data types, look up

Filter Functions Feature • These functions help you return specific data types, look up values in related tables, and filter by related values. Lookup functions work by using tables and relationships between them. Filtering functions let you manipulate data context to create dynamic calculations. • CALCULATE ( ) – Expression with filters • DISTINCT ( ) • FILTER ( ) - Subset of another table • RELATED ( ) - Join • FILTERS ( ) • ALL ( ) – Good for ratios, denominators

Information Functions Feature • These functions look at a table or column provided as

Information Functions Feature • These functions look at a table or column provided as an argument to another function and tells you whether the value matches the expected type. For example, the ISERROR function returns TRUE if the value you reference contains an error. • CONTAINS ( ) vs Query Editor (M) • ISBLANK ( ) AND BLANK ( ) • LOOKUPVALUE ( )

Logical Functions Feature • These functions return information about values in an expression. For

Logical Functions Feature • These functions return information about values in an expression. For example, the TRUE function lets you know whether an expression that you are evaluating returns a TRUE value. • IF ( ) • IFERROR ( ) • TRUE/FALSE ( ) • AND/OR ( )

Text Functions Feature • With these functions, you can return part of a string,

Text Functions Feature • With these functions, you can return part of a string, search for text within a string, or concatenate string values. Additional functions are for controlling the formats for dates, times, and numbers. • BLANK ( ) - Null • CONCATENATE ( ) • CONCATENATEX ( ) • FORMAT ( )

Math and Trig Functions Feature • Mathematical functions in DAX are similar to Excel's

Math and Trig Functions Feature • Mathematical functions in DAX are similar to Excel's mathematical and trigonometric functions. However, there are some differences in the numeric data types used by DAX functions. • SUM ( ) • SUMX ( ) • ROUND ( ) – Rounds up • TRUNC ( ) – Removes decimals

Demos

Demos

Questions/Feedback Feature • New User Group Site – www. pbiusergroup. com • Looking for

Questions/Feedback Feature • New User Group Site – www. pbiusergroup. com • Looking for speakers and volunteers • Looking for sponsors • Power. Point files/Documents • Microsoft Data Insight Summit June 12 -13 th • • Next Meetup Group • Date: April ? • Location: NR Computer Learning Center • Topic: TBD • More Information at • Meetup. com/OC-Power-BI-User-Group • Need help with data modeling • Email: [email protected] com • Phone: 714 -222 -7760 • Pbiusergroup. com forums