Power BI Cheat Sheet

Modifies the filter context of an expression, enabling advanced calculations like conditional totals or time intelligence analysis.

Adds all numerical values in a column, commonly used in measures.

Calculates the average of a column's numerical values.

Calculates the Year-To-Date total for an expression.

Returns the last date in a given date column, often used in semi-additive measures.

Creates a string that shows the hierarchical path from a parent to a child.

Activates an inactive relationship between two tables for a specific calculation.

Generates a continuous range of dates between two specified dates.

Automatically creates a date table based on the minimum and maximum values of date columns in the model.

Retrieves a related value from another table using an existing relationship.

Combines two values into a single text string.

Performs division while handling divide-by-zero errors gracefully by returning an alternate result.

Evaluates a condition and returns different results based on whether the condition is true or false.

Counts the number of unique values in a column.

Returns the rank of a value in a table based on an expression, with options for handling ties.

Evaluates an expression and returns a matching result for the first case.

Formats a value as a string using a specified format (e.g., currency, percentage).

Returns a single-column table with unique values from the specified column.

Refers to a row context in an earlier iteration of the same calculation.

Removes all filters except those applied to the specified columns.

Returns a table that contains only rows matching a specified condition.

Removes all filters from a table or column, returning all rows.

Returns TRUE if the column contains only one distinct value in the current filter context.

Returns the largest numeric value in a column.

Returns the smallest numeric value in a column.

Counts the number of rows in a table.

Groups data by specified columns and calculates aggregated values.

Returns the value of a result column based on search criteria.

Returns TRUE if the specified value is blank.

Rounds a number to the specified number of digits.

Adds two numerical values or columns.

Subtracts one numerical value or column from another.

Multiplies two numerical values or columns.

Divides one numerical value or column by another.

Raises a number to the power of another number.

Combines two text strings into one.

Compares two values for equality.

Compares two values for inequality.

Checks if one value is greater than another.

Checks if one value is less than another.

Checks if one value is greater than or equal to another.

Checks if one value is less than or equal to another.

Returns TRUE if both conditions are true, otherwise FALSE.

Returns TRUE if at least one condition is true, otherwise FALSE.

Reverses the logical value of an expression (TRUE to FALSE and vice versa).

Checks if a value exists in a specified list of values and returns TRUE or FALSE.

Groups expressions or specifies the order of operations in calculations.

Creates a table with a single column containing the listed values.

Creates relationships between tables based on key columns.

Workflow includes importing data from sources like Excel or CSV, transforming data in Power Query Editor, building reports, and sharing insights via Power BI Service.

Easily connect to various data sources like Excel, CSV, XML, or databases, and load them into Power BI for further processing.

Clean and shape your data, such as renaming columns, filtering rows, and changing data types, to prepare it for analysis.

Share your reports with others by publishing them to Power BI Service, where dashboards and access can be managed.

Update data source paths or settings to maintain the accuracy of your reports when file locations change.

Allows managing table relationships, creating measures, and organizing data for effective data modeling.

Establish connections between tables using key columns, defining cardinality (e.g., many-to-one, oneto-one).

Automatically identifies and creates relationships between tables based on column names and data types.

Assigns a table as the primary date table, ensuring accurate time-based calculations and filtering.

Use DAX to calculate values like sums, averages, or percentages, enabling dynamic and reusable calculations.

Add new columns derived from existing ones, materialized into the dataset, increasing file size.

Organize columns (e.g., Year > Month > Day) into a hierarchy for drill-down capabilities in visualizations.

Adjusts how tables relate to each other (e.g., many-to-many for flexible connections).

Breaks down hierarchical relationships into a single-level table for simpler analysis.

Analyzes report performance, identifying areas for improvement like visuals or DAX queries.

Pre-aggregate data by grouping and summarizing at desired levels of detail.

Allows filters to flow both ways in relationships, ensuring comprehensive filtering.

Automatically generates date hierarchies for date columns, simplifying time-based analysis.

Generate commonly used calculations like totals or averages without writing DAX.

Combine multiple columns into a single column (e.g., concatenating Year and Month to create "MonthYear").

Identifies and resolves circular dependencies in relationships to avoid calculation errors.

Defines pre-aggregations for tables, improving performance when querying large datasets.

Allows adding visuals such as tables, bar charts, line charts, etc., to represent data insights effectively.

Adds customized hover-over descriptions for enhanced data context in visuals.

Specifies cell colors, data bars, KPI icons, or web links based on field values.

Adds a slicer to filter data interactively by specific criteria (e.g., year or region).

Organizes data logically, highlighting the most relevant information without removing any.

Removes unnecessary data, enabling focus on relevant information in reports.

Adds visual elements like colors or borders to highlight and isolate specific visuals on the canvas.

Allows users to navigate to detailed pages for in-depth analysis by clicking on a specific field or value.

Saves the current view of a report page for quick access, enhancing navigation and storytelling.

Adds interactive buttons for navigation or specific actions in a report.

Aligns and evenly distributes visuals on the canvas for a clean, professional layout.

Groups data into hierarchical levels (e.g., Year > Month > Day) for drill-down capabilities.

Ensures accessibility by providing descriptions for visuals that screen readers can interpret.

Tracks performance against a target using metrics, goals, and timelines (e.g., total sales vs. sales target).

Analyzes and groups similar data points in a scatter chart based on attribute values.

Displays the top N records based on a selected value (e.g., Top 10 selling products).

Represents statistical data distributions for insights into data patterns.

Provides AI-generated insights into why data has changed or it distribution differences.

Applies pretrained machine learning models for tasks like sentiment analysis, image processing, or anomaly detection.

Identifies and ranks factors that influence a specific metric, aiding in root cause analysis.

Allows data exploration across multiple dimensions, providing insights into high and low values in hierarchical data.

Uses natural language processing to generate visual answers to datarelated questions.

Provides quick descriptive statistics like averages, distributions, and clusters for high-level insights.

Imports specialized visuals from Microsoft AppSource for complex analytical needs.

Tracks and visualizes data trends over time, useful for identifying disruptions or seasonal patterns.

Filters data interactively by categories such as time or geography, enabling segmentation of data.

Groups continuous data (e.g., numerical values or dates) into equal intervals for better analysis.

Automatically generates insights using machine learning algorithms, ideal for initial dashboard exploration.

Converts the first row of data into column headers.

Updates query names to make them meaningful and organized.

Updates column names for clarity and organization of the dataset.

Substitutes specific values or nulls with new ones to resolve inconsistencies or make values user-friendly.

Deletes unnecessary rows to clean the dataset and improve analysis accuracy.

Deletes unwanted columns or retains only necessary ones.

Eliminates duplicate values to ensure accuracy and uniqueness in data.

Summarizes data by converting rows into columns using aggregate functions like SUM, COUNT, or AVERAGE.

Converts column data into rows, enabling easier analysis of wide datasets.

Divides a column into two or more based on a specified delimiter (e.g., comma, space).

Merges multiple columns into one, improving readability or preparing data for analysis.

Reorders data within a column alphabetically, numerically, or by other criteria.

Aggregates data based on selected columns to perform calculations like sums or counts.

Combines data from multiple queries into one using a join operation (e.g., INNER JOIN, OUTER JOIN).

Creates a copy of a column for further transformations without altering the original.

Switches rows to columns or columns to rows for reformatting data.

Fills blank cells in a column with values from the cell above or below.

Consolidates data from multiple files (e.g., Excel or CSV) into one query.

Fixes data type issues by selecting the correct type for calculations and transformations.

Identifies irregularities or quality issues in data through visual summaries like column statistics and value distributions.

Edits the underlying M code for advanced or customized transformations.

Adds a new column based on defined conditions (e.g., categorizing data into ranges).

Enables you to organize and collaborate on dashboards, reports, and datasets.

Defines user permissions for collaboration and content management within the workspace.

Manages content migration between development, testing, and production environments (Premium accounts only).

Visualizes relationships between workspace artifacts (reports, datasets) and external dependencies.

Tracks performance and user interaction metrics for reports and dashboards.

Protects sensitive data by specifying export permissions.

Automates data updates to ensure reports and dashboards display the latest information.

Endorses high-quality datasets for organizational use, ensuring reliability and consistency.

Simulates role-specific views to verify correct implementation of row-level security.

Connects on-premises data sources to Power BI cloud services, enabling seamless data access and refresh.

Enables dynamic filtering of reports based on user input or predefined values.

Runs scenario analysis by dynamically changing parameter values (e.g., forecasting sales impact with variable discounts).

Organizes data into fact and dimension tables for optimized performance and analysis.

Dynamically changes calculations in reports based on input parameters.