Skip to main content Skip to navigation
Mauricio Featherman Mauricio Featherman Ph.D

Analytics Videos and Reading Materials

 

Here is the link to the Power BI guided learning from Microsoft.

PowerBI answers the decades long question: “Why doesn’t Excel manage data better and make interactive charts easy to make, publish, and update? ” For business managers and staff, PowerBI is the best innovation since Excel version 1.0 in 1987. Let us now see why.

Project #1
Booking a hotel for a vacation after looking at neighborhood safety data
a) Live link to the published Power BI report
b) Video – Intro project – booking a hotel a.

TSQL GROUP BY Queries

Putting Records into groups CASE(), NTILE(), GROUP BY()

read this documentation: Categorizing Sales Reps and Employees with CASE

Creating Tables

Creating Tables and Foreign Key Relationships

This next video shows an intro to PowerBI. You can disregard the last section on inserting the embed code into an ASP.NET page.

Interesting video on data visualization

DB Diagram AWDW2012

 

Creating Measures and Using them in Interactive Charts

So a measure is a formula that is not stored in columns of a table. A measure is a formula that is calculated on the fly’ whenever the table or chart is made in Power BI. This is important because the measure can be sliced and will calculate for each of the slicer values, for example calculating average sale amount for each region in a chosen country. The combination of categorical dimensions chosen to filter and scope down the analysis (called slicers) together create the ‘evaluation context‘ so that the measure is calculated depending in the specific need. For example the analyst may choose to draw a column chart or map showing the average sale amount for each of the product lines, in the UK, by month for 2016 (line chart). Savvy analysts recognize the evaluation context as the WHERE clause or HAVING clause in a SQL query.

  1. Read this link to get started with measures
  2. Click this link to work on a tutorial to learn measures. 
  3. Contoso Sales Sample for Power BI Desktop. This is the data file used in the tutorial above.

Measures are created with the powerful Data Analysis Expressions (DAX) formula language written specifically for business analysts. As such working with DAX feels very similar to creating formulas in Excel of course however many new shortcuts and advancements are introduced to simplify the procedures. DAX is becoming as powerful as SQL.

Creating New Columns of Derived Data

Just as in Excel, you can create new columns based on calculation data in Power BI. An importance difference is that in excel you may type =B2 * C2 (and copy the formula down the page), and in PowerBI the formula would look like [Inventory Value] = Stockqty * SalesPrice. In powerBI the columns are imported and stored as datatables so they are referenced by column name. The data that is imported into PowerBI from a database that can get refreshed automatically.

Unlike measures that are calculated on the dashboard when they are dragged into an evaluation context (ie dragged into a chart with slicers) calculated columns are calculated for every row of a table, and the data is stored in the table.

DAX Measures in PowerBI

  1. This tutorial also uses the Contoso sales datafile above and teaches DAX formulas by focusing on SyntaxFunctions, and Context.