Power BI Cheat Sheet: DAX, Visuals, Power Query and More

Power BI is a leading business intelligence tool designed to convert data into actionable insights through dynamic dashboards and visualizations. To maximize its potential, users must understand its key components such as DAX functions, Power Query, visuals, and automation. This cheat sheet provides an in-depth guide to help professionals effectively use Power BI.

Introduction to Power BI

Power BI enables organizations to:

• Connect to various data sources.
• Transform raw data into meaningful structures.
• Visualize and share insights interactively.

With robust tools like DAX for calculations, Power Query for data preparation, and rich visualization options, Power BI is an all-in-one solution for data analysis.

Getting Started

• Data Sources: Connect to Excel, SQL Server, SharePoint, Web, or other platforms.
• Data Import: Use "Get Data" to load data into Power BI.
• Data Model: Relationships between tables, such as one-to-many, can be managed in the Model View.

Power BI Desktop Cheat Sheet

Power BI Desktop is the primary environment for building and designing reports. Its interface includes:

• Data View: Review and clean data tables.
• Model View: Define and manage relationships between datasets.
• Report View: Create and format visuals and dashboards.

Data Modeling

1. Relationships:
   • Define connections between tables using primary and foreign keys.
   • Types of relationships:
     • One-to-Many (1:M): Common for most datasets.
     • Many-to-Many (M:M): Requires special attention to avoid performance issues.
     • One-to-One (1:1): Less common but useful for lookup tables.
2. Data Types:
   • Ensure each column has an appropriate data type (e.g., Text, Date, Number) for efficient processing.
3. Measures and Calculated Columns:
   • Measures: Dynamic calculations defined using DAX (e.g., SUM(Sales[Revenue])).
   • Calculated Columns: Computed fields added directly to a table.(e.g., NewColumn = Table[Column1] + Table[Column2])

Power BI DAX Cheat Sheet

Data Analysis Expressions (DAX) is the core formula language for Power BI, used to create calculated columns, tables, and measures. Here are some essential functions:

Aggregation Functions

• SUM: Adds all values in a column.
  • Example: SUM(Sales[Revenue]) totals the revenue in the Sales table.
• AVERAGE: Calculates the mean value.
  • Example: AVERAGE(Sales[Profit]) returns the average profit.

Logical Functions

• IF: Evaluates a condition and returns specified values.
  • Syntax: IF(Condition, Value_If_True, Value_If_False)
  • Example: IF(Sales[Profit] > 1000, "High", "Low").
• SWITCH: Evaluates an expression against a list of values.
  • Example: SWITCH(Sales[Region], "North", "Excellent", "South", "Good", "Others").

Time Intelligence Functions

• DATESYTD: Returns dates from the start of the year to the last date in the dataset.
  • Example: TOTALYTD(SUM(Sales[Revenue]), 'Date'[Date]) calculates year-to-date revenue.
• PREVIOUSYEAR: Compares values with the same period in the prior year.

Filtering Functions

• CALCULATE: Applies filters to modify context for calculations.
  • Example: CALCULATE(SUM(Sales[Revenue]), Region[Name] = "East").

Lookup Functions

• RELATED: Retrieves related data from another table via a relationship.
  • Example: RELATED(Region[Description]) gets region descriptions.

Pro Tip: Always break complex formulas using variables (VAR) for better readability and troubleshooting.

Power Query Cheat Sheet

Power Query is Power BI’s data preparation engine, allowing users to clean, shape, and transform data.

Connecting to Data Sources

• Access data from Excel, databases, web services, or APIs.
• Select "Get Data" from the Home ribbon and choose the source.

Common Transformations

• Remove Columns: Eliminate unnecessary columns by selecting and right-clicking "Remove Columns."
• Add Columns:
  • Custom Columns: Create a new column using M-code or existing columns.
    • Example: Add a column with conditional logic like if [Sales] > 100 then "High" else "Low".
  • Duplicate Columns: Use “Duplicate” to create a copy for further analysis.
  • Splitting Columns: Split data using delimiters or by a fixed number of characters

Merging and Appending Queries

• Merge Queries: Combine data from multiple tables based on common fields.
  • Example: Merging Customer and Orders tables on CustomerID.
• Append Queries: Stack datasets, useful for consolidating files from the same structure.

Grouping and Summarizing Data

• Group By: Aggregate data based on key fields.
  • Example: Group sales data by region and summarize revenue.

Pivot and Unpivot Data

• Pivot Columns: Transform rows into columns for summary views.
• Unpivot Columns: Convert columns back into rows.

Best Practices:

• Rename steps for better clarity in the applied steps pane.
• Regularly refresh and preview data for accuracy.

Power BI Visuals Cheat Sheet

Power BI offers a variety of visuals to represent data effectively. Here’s a breakdown of Power BI Charts Cheat Sheet:

Basic Visuals

• Bar/Column Charts: Ideal for comparisons across categories.
  • Example: Compare sales figures by product category.
• Line Charts: Display trends over time.
  • Example: Monthly revenue growth.

Proportional Visuals

• Pie and Donut Charts: Showcase proportions.
  • Use sparingly as they can be hard to interpret with many slices.

Tabular Visuals

• Tables: Display raw data in tabular form.
• Matrix: Similar to pivot tables, useful for hierarchical data.

Advanced Visuals

• Scatter Plot: Analyzes relationships between two numeric variables.
  • Example: Relationship between marketing spend and revenue.
• Waterfall Chart: Illustrates cumulative effects.
  • Example: Breakdown of profits across multiple departments.
• Decomposition Tree: Drills into hierarchical data.
  • Example: Analyzing total sales by region and further by product category.

Tips for Customization

• Use consistent color schemes.
• Adjust axes labels and titles for clarity.
• Include tooltips for additional context.

Filters

• Filter Pane: Page-level, Report-level, or Visual-level filters.
• Slicers: Add interactivity for users to filter data dynamically.
• Drill-through: Navigate between pages using specific filter contexts.

Power FX Cheat Sheet

Power FX is the low-code formula language for app customization and enhancing Power BI dashboards.

Common Functions

• Text: Format text-based calculations.
• If: Perform conditional logic in apps or reports.

Applications:

• Use to add calculated fields in apps or embed in dashboards.

Power Automate Cheat Sheet

Power Automate integrates automation into Power BI workflows.

Use Cases:

• Automate report distribution (e.g., send PDFs via email).
• Trigger workflows based on data refresh events.

Basic Steps:

• Define a trigger (e.g., Power BI dataset update).
• Add actions like email notifications or data transfer.

Templates:

• Utilize pre-built workflows available in Power Automate.

Chart Selection Guide

The success of your Power BI report often hinges on selecting the right chart:

• Use Line Charts for time trends.
• Use Stacked Charts for cumulative data comparisons.
• Use Maps for geographical insights.

Avoid: Overloading charts with too many data points.

Shortcuts

• Ctrl + C / Ctrl + V: Copy/Paste visuals.
• Ctrl + Z / Ctrl + Y: Undo/Redo actions.
• Ctrl + Shift + L: Clear all filters.
• Alt + Shift + Arrow: Align visuals.
• Ctrl + R: Rename selected visuals.
• Ctrl + Shift + End: Clear filters for all visuals.

Tips for Enhanced Productivity

• Save and reuse templates for recurring projects.
• Optimize model performance by reducing column data types.
• Enable drill-through for in-depth analysis.

Conclusion

This comprehensive Power BI cheat sheet serves as an essential guide for harnessing the platform’s capabilities. By mastering DAX, Power Query, and visualization techniques, users can create insightful and impactful reports. Utilize these tools and tips to streamline data processes and maximize the value derived from Power BI.

More Cheat Sheets and Top Picks

• SQL Cheat Sheet
• PySpark Cheat Sheet
• Git Cheat Sheet
• Pandas Cheat Sheet
• DSA Cheat Sheet
• Python Cheat Sheet