Microsoft Certified: Power BI Data Analyst Associate flashcards

Hybrid data connectivity in Power BI involves combining different storage modes (e.g., DirectQuery and Import) for different tables within a single data model to optimize for both performance and data freshness requirements.

• Combines Import and DirectQuery modes.
• Optimizes performance for static data, delivers freshness for dynamic data.
• Requires careful consideration of data update frequency and query needs.

Memory trick: Freshness for fast, Performance for stable.

The logical order of applying Power Query transformations to effectively clean and standardize data, often starting with formatting (trimming, casing) before addressing missing values, errors, or duplicates.

• Order matters for effective cleaning.
• General flow: Format -> Handle Missing/Errors -> Standardize -> Remove Duplicates.
• Trimming spaces should precede handling empty/null values.

Memory trick: First polish it, then check for holes, then throw out the trash.

A Power Query transformation that allows users to select a subset of columns to keep in the table, effectively removing all unselected columns.

• Crucial for optimizing data model size and performance.
• Removes unnecessary columns, including intermediate helper columns.
• Should typically be one of the final steps in a query for efficiency.

Memory trick: To keep what you need, 'Choose Columns' indeed.

A DAX time intelligence function that returns a table containing all dates from the previous month, based on the first date in the current filter context.

• Part of Power BI's time intelligence functions.
• Used to compare current period data with the immediately preceding month.
• Requires a marked date table.

Memory trick: Time travel functions: Previous month, year, or custom period.

A DAX time intelligence function that returns a table containing dates that are shifted one year back from the dates in the current filter context, preserving the period.

• Compares current period with the equivalent period in the previous year.
• Automatically handles year-over-year comparisons.
• Requires a marked date table.

Memory trick: Same period, just last year.

When a measure is used in a Power BI visual alongside dimension columns, the visual automatically establishes a filter context for each combination of those dimension values. The measure is then evaluated within this specific filter context.

• Visuals create filter contexts implicitly.
• Each row/cell in a visual has its own filter context.
• Measures respond dynamically to these contexts.

Memory trick: A visual is like a spotlight, shining on a specific part of your data.

A structured arrangement of columns within a dimension table that represents different levels of granularity, allowing users to navigate data from general to specific.

• Enables drill-down and drill-up functionality in reports.
• Typically built within dimension tables.
• Improves user experience and data exploration.

Memory trick: Hierarchies branch out, making data digestible.

X-functions (e.g., SUMX, AVERAGEX, COUNTX) are iterator functions in DAX that evaluate an expression for each row of a specified table, then perform an aggregation. They are crucial for row-context calculations.

• Evaluate an expression row by row.
• Create a row context for each iteration.
• Can be used to perform calculations that depend on individual row values.
• Often used with RELATED or RELATEDTABLE to cross relationships in row context.

Memory trick: X-functions iterate rows, then calculate; AVERAGEX over SUMX for average of sums.

The 'Split Column by Delimiter' transformation in Power Query divides a single text column into multiple columns based on occurrences of a specified character or string.

• Splits one column into many.
• Requires a defined delimiter (e.g., comma, pipe, tab).
• Can handle multiple occurrences of the delimiter.

Memory trick: Delimiters are the scissors for your text lines.

A Power Query feature allowing users to convert a column's data type (e.g., Text to Date/Number) while specifying a cultural locale, which helps in correctly interpreting varied number or date formats specific to that region.

• Handles varied date/number formats from different regions
• More robust than simple 'Change Type' for inconsistent data
• Accessed via 'Using Locale...' option during type change
• Reduces need for complex custom M functions

Memory trick: Locale Converts: Understand the Culture, Parse the Date.

A multi-step Power Query process involving combining files, selecting specific sheets, handling header rows, and unpivoting data to transform messy source data into a clean, tabular format.

• Order of operations is critical for correct transformation.
• Folder connector simplifies combining multiple files.
• Remove Top Rows and Promote Headers are key for non-standard data layouts.
• Unpivot is essential for converting column-based attributes into rows.

Memory trick: Folder's combined, sheet is found, rows removed, headers crowned, then unpivot all around.

A Power Query transformation that converts a column's data type while considering cultural formatting rules. This is particularly useful for parsing dates, times, and numbers that vary by region.

• Handles varied date, time, and number formats.
• Leverages cultural settings for parsing.
• Accessed via 'Using Locale...' option in Change Type menu.

Memory trick: Dates are global travelers; locales help them fit in anywhere.

A Mixed Storage Mode strategy in Power BI involves using different data connectivity modes (e.g., DirectQuery, Import) for different tables or parts of a data model, optimizing for diverse requirements such as real-time freshness for operational data and performance for historical analytical data.

• Combines DirectQuery and Import modes.
• Balances real-time needs with query performance.
• Tailors connectivity to specific data source characteristics and reporting needs.

Memory trick: Fresh for fast, Stored for deep, both make the insights leap.

The 'Expand Record' transformation in Power Query flattens a column containing records (nested objects) into new columns, allowing access to the individual fields within each record.

• Used for nested JSON objects or structured data.
• Converts a record column into multiple columns.
• Makes nested data accessible for reporting.

Memory trick: Expand the box to see all the items inside.

A Power Query transformation that allows you to navigate into a structured column (containing records, lists, or tables) and select its internal fields or elements to be promoted as new top-level columns in the current table.

• Used for nested data structures (records, lists, tables).
• Promotes internal fields to new columns.
• Crucial for flattening complex data sources like JSON/XML.

Memory trick: Expand is like opening a box and taking out what's inside to put it on the shelf.

The process of isolating specific components (like year, month, day) from a date or datetime column in Power Query for analytical purposes.

• Requires the column to be of a Date or DateTime data type.
• Power Query provides built-in transformations for common date parts.
• Ensures accurate extraction, unlike text manipulation.

Memory trick: Date must be a date, then extract the part you crave.

Applying date-based filters within a DAX measure that adjust based on a dynamic 'current date' (e.g., TODAY() or a selected date in a slicer).

• Uses CALCULATE and FILTER functions.
• Compares a date column to a dynamic date expression (e.g., TODAY() - 90).
• Ensures calculations reflect the current context or system date.

Memory trick: Today's date minus time, then filter and calculate.

DATESINPERIOD is a DAX time intelligence function that returns a table that contains a column of dates starting with a given start date and continuing for the specified number of intervals.

• Used for defining dynamic date ranges.
• Essential for rolling calculations like moving averages.
• Requires a start date, number of intervals, and interval type (e.g., DAY, MONTH, YEAR).
• Returns a table of dates that can be used within CALCULATE.

Memory trick: Time intelligence functions are like a calendar, helping you navigate through dates.

A DAX iterator function that evaluates an expression for each row of a table and then sums the resulting values.

• Syntax: SUMX(<table>, <expression>).
• Essential for calculations that need to happen at the row level before aggregation (e.g., Price * Quantity).
• Creates its own row context for the expression.

Memory trick: SUMX iterates, calculates, then sums.

A Power Query transformation used to flatten a column containing structured record values (like those from JSON or nested tables) into new columns, exposing the fields within the record.

• Used for nested data structures (JSON, records)
• Converts a single record column into multiple new columns
• Essential for flattening hierarchical data

Memory trick: Expand Records: Unpack the Nested Tree into a Flat Table.

Calculated columns store values for each row in the model and are computed during data refresh, consuming memory. Measures are calculated on-the-fly at query time based on the current filter context and do not consume memory for storage.

• Calculated columns: row-level, stored in model, refreshed with data.
• Measures: aggregated, calculated at query time, context-dependent.
• Use measures for aggregations to optimize performance.
• Use calculated columns for static row-level attributes (e.g., age from birthdate).

Memory trick: Optimize by thinking 'measure first' for aggregations, not 'column always'.

The 'Choose Columns' transformation explicitly selects which columns to retain. When applied to foldable data sources (like SQL databases), Power Query can translate this operation into a native query (e.g., a SQL SELECT statement), reducing data transferred and improving performance through 'query folding'.

• Selects a subset of columns to keep.
• Directly supports query folding for relational databases.
• Reduces data volume fetched from source.

Memory trick: Folding queries is like sending a precise shopping list to the database, not buying the whole store.

A cumulative sum, or running total, is a measure that aggregates values sequentially over a specified dimension, typically time, accumulating the total as new data points are added.

• Aggregates values from the beginning of a period up to the current point.
• Often requires modifying filter context to include past periods.
• Can be challenging to implement while respecting other dimensions (e.g., customer, product).
• ALLSELECTED is useful for running totals that should respect external filters but ignore internal table filters.

Memory trick: Running totals are like a marathon: keep adding to the distance covered.

A Power BI App is a collection of dashboards, reports, and workbooks that you can share with a broad audience, providing a simplified navigation experience and centralized permission management.

• Curated content distribution
• Simplified navigation
• Centralized permission management
• Replaced content packs

Memory trick: Apps pack content for easy access to your group.

A line chart with the area between the line and the x-axis filled, often used to display the magnitude of change over time and for cumulative or part-to-whole relationships over a continuous axis.

• Excellent for showing trends over time.
• Effective for cumulative data.
• Can compare multiple series with different colors/shading.

Memory trick: Area Grows, Adoption Shows

A bullet chart is a variation of a bar chart developed by Stephen Few. It displays a primary measure, compares it to one or more target measures, and provides qualitative ranges (e.g., good, bad, satisfactory) to give context and indicate performance.

• Compares a single measure to multiple targets/benchmarks.
• Includes qualitative ranges for performance context.
• Efficient use of space, high data density.

Memory trick: Aim for the target, shoot for the best, a bullet chart puts your performance to the test.

A visual combination used to display trends over time, mark a significant threshold, and visually alert users when data points cross that threshold.

• Line charts excel at showing time-based trends.
• Reference lines provide a static comparison point.
• Conditional formatting adds dynamic visual cues based on rules.

Memory trick: Sentiment's Line Dips, Red Alert!

A shared Power BI dataset is a dataset published to the Power BI service that can be reused by multiple reports, promoting data consistency and simplifying maintenance.

• Single source of truth
• Reusable by multiple reports
• Reduces data duplication
• Simplifies data model maintenance

Memory trick: Share the dataset, share the truth, share the work.

The SAMEPERIODLASTYEAR DAX function returns a table that contains a column of dates shifted back one year in time, but in the same period as the dates in the specified 'dates' column. It's crucial for year-over-year comparisons.

• Time intelligence function.
• Returns a single column of dates.
• Used within CALCULATE to apply the date filter context.
• Requires a contiguous date table for optimal performance.

Memory trick: Last year's same period, a DAX function clear, SAMEPERIODLASTYEAR brings the data near.

A specialized chart, often sourced from Power BI's AppSource, used for project management to visualize project schedules, showing start/end dates, durations, and task dependencies over time.

• Ideal for project timeline visualization.
• Shows task durations and overlaps.
• Typically found in Power BI AppSource as a custom visual.

Memory trick: Gantt charts are the project manager's best friend, timelines and tasks from start to end.

The Power BI REST API includes endpoints that allow external applications or systems to programmatically trigger dataset refreshes, enabling event-driven automation and integration.

• Enables programmatic refresh
• Triggered by external systems/events
• Integrates with automated pipelines
• Requires API calls

Memory trick: The REST API listens for events to trigger the refresh.

Dedicated capacities (Premium, Embedded, Fabric) in Power BI are assigned at the workspace level to provide reserved resources for performance and isolation for all content within that workspace.

• Assigned to a workspace
• Provides dedicated resources
• Ensures performance and isolation

Memory trick: The workspace is the box, and you assign the whole box to a strong capacity.

A visual that displays hierarchical data as a set of nested rectangles, where the size of each rectangle is proportional to its value.

• Good for showing proportions and part-to-whole relationships.
• Effective for hierarchical data structures.
• Interactive, allowing drill-down into categories.

Memory trick: Tree of Boxes, Sizes Tell the Tale

An interaction behavior in Power BI where selecting data points in one visual dims the unselected data in other visuals while emphasizing the corresponding selected data, maintaining context.

• Controlled via 'Edit interactions'.
• Emphasizes selected data without filtering out other data.
• Useful for showing relationships within a broader context.

Memory trick: Highlight your selections, see the context still, cross-highlighting, a visual skill.

A Power BI Service feature that automatically updates imported datasets from their original data sources at predefined intervals.

• Applies to imported datasets.
• Configured in Power BI Service.
• Requires a gateway for on-premises sources.

Memory trick: Imported data needs a schedule to stay fresh.

Import storage mode in Power BI copies data from the source into the Power BI dataset, caching it locally for faster query performance and reduced source database load.

• Data cached locally
• Fast query performance
• Reduces source database load

Memory trick: Import the data into Power BI's fast lane.

One of the three main views in Power BI Desktop, used for inspecting data, creating calculated columns and measures, and viewing table structures.

• Shows raw data in tables.
• Used for DAX formula creation.
• Allows column property inspection.

Memory trick: R-D-M, Report-Data-Model, know where to roam.

A Power BI feature that restricts data access at the row level based on user roles and filters. Users only see the subset of data they are authorized for.

• Filters data based on user identity.
• Implemented by defining roles and DAX filter expressions.
• Managed in Power BI Desktop and enforced in Power BI Service.

Memory trick: Secure your rows, secure your columns, data access is never solemn.

The Power BI On-premises Data Gateway acts as a secure bridge, providing quick and secure data transfer between on-premises data sources and Microsoft cloud services like Power BI.

• Required for scheduled refreshes of on-premises data.
• Manages data source credentials securely.
• Encrypts data in transit.

Memory trick: Gateway Guards On-premises Gold.

Power BI 'Embed for your customers' (app owns data) is an embedding solution where the web application handles authentication and data access, ideal for external or large internal audiences without requiring individual Power BI licenses.

• Application manages authentication
• App controls RLS and data access
• No Power BI licenses required for end-users
• Highly scalable

Memory trick: Your app takes ownership to serve your customers.

Microsoft Purview Information Protection auto-labeling policies automatically apply sensitivity labels to content based on conditions, ensuring consistent data governance and compliance.

• Automates label application
• Based on content or location
• Ensures data governance compliance
• Reduces manual effort

Memory trick: Purview's auto-labeling policies are the robot for data compliance.

Controls access to a curated collection of Power BI reports and dashboards, distributed as an 'App' to specific users or groups.

• Distributes content to a broad audience.
• Provides read-only access by default.
• Managed via App audience settings.

Memory trick: Apps deliver reports, permissions control the view.

A Power BI visual that combines a column chart and a line chart on the same axes, often used to compare two related measures where one might be discrete counts and the other a continuous average or target.

• Good for showing actual vs. target/average.
• Columns for individual period values, line for trend/average.
• Can use dual Y-axes for different scales.

Memory trick: Columns for Now, Line for History's Flow

Dataset refresh in Power BI is the process of updating the data in a published dataset from its original data sources. It can be scheduled or performed on demand.

• Essential for data currency in reports.
• Requires gateway for on-premises sources.
• Can be scheduled daily, hourly, etc.

Memory trick: Refresh Schedule, Not Permissions, Updates Data.

Power BI visuals can interact in two primary ways: 'Cross-highlight' dims unselected data while keeping it visible, whereas 'Filter' removes unselected data, showing only the selected portion in the target visual.

• Cross-highlight: dims unselected, keeps all data points.
• Filter: removes unselected, shows only selected data points.
• Configured in 'Edit interactions' mode.

Memory trick: Highlight dims, Filter hides; choose wisely how your data abides.

A feature in Power BI Desktop that allows users to customize how visuals on a report page filter or highlight each other when a selection is made.

• Controls visual-to-visual communication.
• Options include Filter, Highlight, or None.
• Can be configured for one-way or bidirectional interaction.

Memory trick: Edit interactions, set them just right, visuals filter each other, day and night.

For each on-premises data source (e.g., SQL Server, Oracle) that Power BI service needs to access via a gateway, a corresponding data source connection must be configured within the gateway, including its specific credentials.

• Gateway acts as a bridge, but individual data sources need configuration.
• Credentials for each data source are stored securely within the gateway.
• Essential for scheduled refreshes of on-premises data.

Memory trick: Gateway Needs Data Source Details.

Object-Level Security in Power BI allows you to secure specific tables or columns in a dataset, making them invisible to unauthorized users, regardless of the report.

• Applied at the dataset level.
• Hides entire tables or columns from users.
• Implemented using Tabular Editor 2 or other external tools.

Memory trick: OLS Obscures, RLS Restricts Rows.

Power BI's 'Group' function (also known as binning) allows users to categorize numerical data into custom-sized groups or bins. This is useful for analyzing distributions and trends across ranges rather than individual data points.

• Creates new, grouped categories from numerical columns.
• Accessible from the Fields pane by right-clicking a column.
• Can define fixed-size bins or custom lists of groups.

Memory trick: Turn numbers into boxes, group ages for clear focus.

A Power BI feature that enables users to navigate from a data point in one report page (source) to another report page (destination), with the destination page automatically filtered by the context of the selected data point.

• Passes filter context from source to destination page.
• Used for detailed analysis on a specific item.
• Requires defining drill-through fields on the destination page.

Memory trick: Navigate through details, drill deep down, contextually filtered, without a frown.

A bar chart where each bar represents a total, and segments within the bar represent the percentage contribution of different categories to that total, summing to 100%.

• Excellent for showing part-to-whole relationships as percentages.
• Ideal for comparing proportional distributions across different groups.
• Easy to see relative contributions of each segment.

Memory trick: Bars Stacked, Percentages Packed

Configuration settings within Power BI deployment pipelines that allow modifications to data source connections, parameters, and RLS during content promotion between stages.

• Applies during content promotion.
• Includes data source and parameter rules.
• Ensures stage-specific configurations.

Memory trick: Rules guide the pipeline's moves.

A data mining technique used in retail to identify strong associations or co-occurrences between groups of items (e.g., products frequently purchased together).

• Discovers 'what goes with what' patterns.
• Often uses metrics like support, confidence, lift.
• Can be implemented in Power BI via custom visuals or scripting.

Memory trick: Basket analysis finds links, what products are bought, what the customer thinks.

A feature in Power BI Service that enables a structured release process for Power BI content across development, test, and production environments.

• Supports multiple stages (Dev, Test, Prod).
• Facilitates content promotion.
• Reduces deployment risks.

Memory trick: Pipelines push reports through production.

A Power BI visual that combines a column chart and a line chart, allowing for the display of two different measures, often with different scales, on the same visual.

• Effective for comparing two related measures.
• Can use a secondary Y-axis for different scales.
• Good for showing trends (line) alongside categorical comparisons (columns).

Memory trick: Columns Stand, Lines Flow for Two Tales

A Power BI visual that displays data in a grid format, allowing for hierarchical display of rows and columns and showing aggregated values at their intersections, similar to a pivot table.

• Excellent for multi-dimensional analysis.
• Supports drill-down and drill-up.
• Can display subtotals and grand totals.

Memory trick: Matrix Grids Organize Complex Data

A choropleth map visual in Power BI that shades predefined geographic regions (like countries, states, or postal codes) based on the value of a measure, allowing for quick visual comparison of data across regions.

• Uses standard geographic hierarchies.
• Shades entire regions based on a measure.
• Ideal for showing distribution or intensity across areas.

Memory trick: Map your sales, fill the countries, see the patterns, no more boundaries.

A Power BI feature that enables report designers to control how different visuals on a report page filter or highlight each other when a selection is made.

• Controls visual filtering/highlighting behavior.
• Accessible from the 'Format' tab when a visual is selected.
• Allows 'Filter', 'Highlight', or 'None' interaction types.

Memory trick: Interactions Link Visuals Smoothly

Conditional formatting in Power BI allows you to apply visual formatting (e.g., colors, icons, data bars) to data points or cells in tables and matrices based on specified rules or conditions, making it easier to highlight key information.

• Applies visual styles based on data values.
• Can be used for background color, font color, data bars, icons, and web URLs.
• Applicable to tables, matrices, and some other visuals.

Memory trick: Colors and icons, conditions they meet, make your data speak, oh so sweet.

Power BI Audit Logs provide a detailed record of user and administrator activities within the Power BI service, essential for security, compliance, and auditing.

• Records user and admin activities
• Includes report access, data exports, changes
• Accessible via Microsoft 365 Compliance portal
• Crucial for security and compliance

Memory trick: Audit logs are the detective's detailed history book.