HW1 ‐ Power BI Guide for Beginners - yentingw-gin/BIDD330_Spring2025_Reporting GitHub Wiki

Overview

This guide is designed for individuals new to Power BI, especially those building a dashboard for the first time. We'll walk through the essential steps to create a Power BI report from scratch using data imported from SQL Server. The article covers the following topics:

  • Get the data: How to connect to a SQL Server database and import tables into Power BI
  • Build the data model: Step-by-step guidance and best practices for creating a solid data model
  • Create and organize measures: Introduction to DAX and organizing your key calculations
  • Work with visuals: How to design interactive and insightful visualizations
  • Publish the report: How to share your finished dashboard with others using Power BI Service

Get The Data

  1. Open Power BI Desktop and click on Home > SQL Server
  2. In the SQL Server database window:
    • Enter the server name (e.g., servername.database.windows.net)
    • Enter the database name (optional)
    • Select Import or DirectQuery as your connection method
      • Use Import for faster performance and offline capabilities
      • Use DirectQuery to query data live (slower, but always up to date)
    • Click OK
  3. Choose the tables you need
  4. Click Load
    • Use Transform Data if you need to clean or filter data before loading

Power BI - Get Data via Server

Build The Data Model

  1. Go to Model View, then click Manage relationships
  2. Click New relationship in the Manage relationships window
  3. In the New relationship window:
    • Configure the table relationship
    • Repeat this step to add additional relationships as needed
    • Once you're done, click Close
  4. When you've finished designing the model, click Save in the Manage relationships window
  5. Review the Entity Relationship (ER) Diagram automatically generated in Power BI.

Power BI - Build the Data Model

Create And Organize Measures

  1. Watch this video on how to organize measures with a table for easy access
  2. Select the mesure table created in Step 1, then click New Measure
  3. Enter the DAX code to create your measure:
    • Repear for other KPIs as needed
    • Use DAX variables (VAR) to simplify complex formulas.

See the following DAX code for reference:

    Dim Date Total Count Of Month = 
    VAR _start = STARTOFYEAR( FactCovid[Updated]) -1
    VAR _end = ENDOFMONTH( FactCovid[Updated])
    VAR MonthCount = DATEDIFF( _start, _end, MONTH ) + 1
    RETURN IF (MonthCount = BLANK(),0,MonthCount)

    Dim Date Total Row Count = 
    VAR dimdaterow = COUNTROWS(DimDate)
    RETURN dimdaterow

    Dim State States Count = 
    VAR statecount = CALCULATE(COUNT(DimState[State]))
    RETURN statecount

    Fact Covid Country Distinct Count = 
    VAR countCountry = CALCULATE(DISTINCTCOUNT(FactCovid[Country_Region]))
    RETURN IF(countCountry = BLANK(),0,countCountry)

    Fact Unemployment Total Row Count = 
    VAR unemploymentcount =
        COUNTROWS (
            FILTER (
                FactCovid, --when there is a match from this table
                CONTAINS ( FactUnemployment, [State], FactCovid[Admin_Region_1] ) --otherwizard return 0
            )
        )
    RETURN
        IF(unemploymentcount = BLANK(),0,unemploymentcount)

    Total Row Count of Entrie Dashboard = 
    VAR totalrowcount = CALCULATE(
        [Fact Covid Total Row Count]
        +[Fact Unemployment Total Row Count]
        +[Dim Date Total Row Count]
        +[Dim State Total Row Count]
        +[Dim Country Total Row Count]
    )
    RETURN totalrowcount

Work With Visuals

  1. Go to Report View
  2. Choose a visual from the Visualizations pane (e.g., bar chart, pie chart, table)
  3. Drag fields and measures into Axis, Values, or Legend areas
  4. Customize the appearance of the visual using the Format Visual pane

Power BI - Work with Visuals

Publish The Report

  1. Save your .pbix file
  2. Click Home > Publish
  3. Sign in with your Power BI account.
  4. Select a workspace in Power BI Service.

Final Notes

  • Practice makes perfect. Experiment with DAX, visuals, and filters.
  • Use Power BI Community and Microsoft Learn for troubleshooting and tutorials.
  • As your skills grow, explore Power Query, custom visuals, and Row-Level Security (RLS).