Microsoft Certified: Power BI Data Analyst Associate flashcards

Data source credentials in Power BI service provide the authentication details required for the Power BI service (via a gateway for on-premises sources) to connect to the underlying data source for data refresh.

• Required for data refresh
• Stored securely in Power BI service
• Used by gateway for on-premises sources
• Ensures proper authentication

Memory trick: Credentials are the key to unlock the data for scheduled refreshes.

Power BI Tenant Settings allow administrators to control organizational-wide features, including the ability to export data from reports, for all users or specific security groups, supporting data loss prevention.

• Organizational-wide control
• Managed by Power BI administrators
• Can restrict data export
• Supports data loss prevention

Memory trick: Tenant settings are the gatekeeper for data export across the organization.

An 'Invalid credentials' error during Power BI dataset refresh typically means the stored authentication details (username, password, API key, etc.) for the data source are incorrect or have expired.

• Directly relates to stored authentication
• Common for password changes/expirations
• Requires updating credentials in Power BI service

Memory trick: An invalid key means the data door won't open.

Basic authentication for a Power BI data source requires the user to manually enter a username and password every time they attempt to access the data, even if already signed into Power BI.

• Requires manual cred entry each time
• No session persistence
• Less secure than token-based methods
• Directly uses username/password

Memory trick: Basic authentication makes you re-enter your secret every time.

A Power BI visual used to display multiple rows of data, often key performance indicators (KPIs), where each row can have conditional formatting applied to highlight performance against targets or thresholds.

• Excellent for displaying multiple KPIs or categorized metrics.
• Conditional formatting enhances immediate understanding of performance.
• Concise and effective for dashboard summaries.

Memory trick: Cards Show Score, Color Tells More

The Power BI On-premises Data Gateway acts as a secure bridge between Power BI cloud services and on-premises data sources, enabling scheduled data refreshes and Live Connections.

• Connects cloud to on-premises data
• Required for scheduled refresh of on-premises sources
• Required for Live Connection to on-premises sources
• Installed on a local machine

Memory trick: The Gateway is the secure bridge to your home data.

A Power BI Filled map, also known as a Choropleth map, displays quantitative data by coloring geographical regions (e.g., countries, states) with different shades or colors based on the value of a measure. Darker shades typically represent higher values.

• Shades geographical regions based on a measure.
• Requires location data (country, state, province).
• Good for showing regional distribution of a single metric.

Memory trick: Fill the map with color, based on values you adore, see sales across every shore.

A Power BI feature that applies formatting (e.g., colors, icons, data bars) to visual elements based on the values in the data, making it easier to identify patterns, trends, and outliers.

• Enhances readability and highlights important data.
• Can be applied to tables, matrices, and other visuals.
• Rules can be based on numbers, percentages, or field values.

Memory trick: Color-code the bad, the good, the ugly, conditional formatting makes data less fuzzy.

Power BI report subscriptions allow users to receive scheduled snapshots of reports (e.g., as PDF) directly in their email inbox or to a distribution list.

• Automated report delivery
• Scheduled frequency
• Supports various formats (PDF, PPTX, PNG)

Memory trick: Subscribe to get your reports delivered on time, like a newspaper.

A time intelligence function that returns a table that contains all dates from the previous month, based on the first date in the current selection.

• Used within CALCULATE to shift context to the prior month.
• Requires a date column as an argument.
• Useful for month-over-month comparisons.

Memory trick: Time DAX functions are like a calendar, jumping to past dates with care.

A deployment pipeline data source rule maps data source connection properties (like server and database names) between stages, allowing datasets to automatically connect to environment-specific data sources during deployment.

• Automates data source switching
• Configured per stage for datasets/dataflows
• Typically maps server and database names
• Ensures environment-specific connections

Memory trick: Map the server and database to connect the right source.

A configured connection within Power BI Service that allows datasets to access data from cloud-based data sources, such as Azure SQL Database.

• For cloud data sources only (e.g., Azure SQL, Azure Blob Storage).
• No gateway software required.
• Configured directly in Power BI Service.

Memory trick: Cloud needs a cloud connection, on-prem needs a bridge.

A solution for discovering, classifying, and protecting sensitive data across an organization's digital estate, including Power BI.

• Uses sensitivity labels.
• Enforces encryption and access controls.
• Integrates with Power BI for data governance.

Memory trick: Purview protects your data's view.

A Gantt chart is a project management tool that graphically illustrates a project schedule. It shows the start and end dates of tasks, their durations, and can often display progress against a timeline. In Power BI, it's typically available as a custom visual.

• Visualizes project timelines and tasks.
• Shows start dates, end dates, and durations.
• Can often display task progress (e.g., % complete).

Memory trick: Gantt charts lay out tasks like a road map, showing progress from start to end, no gap.

A time intelligence function in DAX that returns a table that contains a column of dates, shifted either forward or backward in time by a specified number of intervals from the current context.

• Used within CALCULATE for time-shifted calculations.
• Requires a date column, number of intervals, and interval type (DAY, MONTH, QUARTER, YEAR).
• Essential for 'previous period' or 'next period' comparisons.

Memory trick: Calculate Dates, Shift Time

When a Power BI dataset has a live connection to an Azure Analysis Services (AAS) model, row-level security (RLS) roles are defined and managed within the AAS model.

• RLS defined in AAS model
• Power BI inherits AAS roles
• Users mapped to AAS roles in Power BI service

Memory trick: The data source holds the keys to its own security rules.

A feature in Power BI Service to package and distribute a collection of reports and dashboards to a large audience with controlled, consistent access.

• Curated content for consumers.
• Centralized access management.
• Read-only by default, preventing changes.

Memory trick: Apps deliver reports, neatly wrapped for all.

The 'Filter' interaction in Power BI causes a selected visual to filter the data displayed in another visual, showing only the data points related to the selection.

• Reduces the data in the target visual.
• Ideal for showing specific details based on a selection.
• Can be configured in the 'Edit interactions' mode.

Memory trick: Interactions shape how visuals dance; Filter makes one visual dance only with the selected partner.

The 'View as roles' feature in Power BI Desktop enables testing of Row-Level Security (RLS) by simulating how a report appears to users assigned to specific roles.

• Located under the Modeling tab in Power BI Desktop.
• Allows selection of one or more RLS roles to simulate.
• Crucial for validating RLS implementation before publishing.

Memory trick: Roles Reveal Rows.

Applying colors, icons, or data bars to visual elements (like columns or bars) in a chart based on specific rules or conditions to highlight important data points.

• Enhances data readability and insights.
• Can be applied to various chart types (bar, column, matrix, table).
• Rules can be based on field values, rules, or color scales.

Memory trick: Target Performance: Colors Reveal Success

Power BI Mixed Mode (Composite Models) allows a single data model to contain data from multiple sources, including a combination of Import and DirectQuery connections, enabling complex data integration and modeling.

• Combines Import and DirectQuery
• Integrates data from diverse sources
• Enables complex modeling and measures
• Offers flexibility in data handling

Memory trick: Mixed mode mixes and matches for maximum data flexibility.

Power BI allows secure storage of credentials, such as API keys or usernames/passwords, for cloud data sources directly within the dataset settings in the Power BI service.

• Stored securely in Power BI service
• Not embedded in PBIX files
• Managed per dataset

Memory trick: Keep your cloud keys safe in the service's vault.

Power BI Tenant Settings are administrative controls configured in the Power BI admin portal to manage various features and security policies across an entire Power BI organization.

• Apply organization-wide
• Control security, sharing, and governance
• Managed by Power BI administrators

Memory trick: The tenant sets the rules for the whole Power BI building.

A Power BI feature that allows report users to dynamically change the measures or dimensions used in a visual by making selections from a slicer, offering greater interactivity and flexibility.

• Creates a slicer for dynamic field selection.
• Can switch between measures or dimensions.
• Enhances report interactivity without needing multiple visuals.

Memory trick: Parameterize your fields, swap dimensions and measures, interactive reports, bring all the pleasures.

A visual that displays the trend of multiple series over time, where each series is stacked on top of another and scaled to represent its percentage contribution to the total at each point in time.

• Shows proportional contribution of parts to a whole over time.
• Good for comparing relative trends, not absolute values.
• Each stack represents 100% of the total at a given point.

Memory trick: Charts help tell tales, each with its own proportional scale.

Designing a Power BI dashboard to effectively display Key Performance Indicators (KPIs) using prominent visuals and default filtering to provide an immediate overview of critical metrics.

• Use Card visuals for single, important numbers.
• Apply default filters for specific time periods (e.g., current month).
• Aim for clarity and minimal interaction for high-level overviews.

Memory trick: Cards for KPIs, filter for now, executive insights, clear somehow.

A Live Connection allows a Power BI report to connect directly to an existing dataset in the Power BI service, ensuring real-time reflection of source dataset updates.

• Connects to existing Power BI dataset
• No data stored in report's model
• Reflects source changes automatically
• Limited modeling capabilities in report

Memory trick: Live connections bring the dataset to life, always fresh.

The Power BI admin portal includes a tenant setting to control whether external guest users can access Power BI content across the entire organization.

• Organization-wide control
• Affects sharing with AAD guest users
• Managed by Power BI administrators

Memory trick: The tenant administrator closes the guest door for the whole building.

Power BI Apps are a way to share collections of dashboards, reports, and workbooks with a broad audience in a curated, user-friendly manner. They simplify content discovery and permission management.

• Distribute curated content to large audiences.
• Centralized permission management.
• Provide a simplified navigation experience.

Memory trick: Apps Assemble Audiences.

A word cloud is a visual representation of text data, where the size of each word indicates its frequency or importance. It's often used for quickly perceiving the most prominent terms and themes within a body of text.

• Visually shows word frequency.
• Useful for identifying dominant themes.
• Often requires text pre-processing (stop words, stemming).

Memory trick: Words form clouds, revealing the themes that loudly shout.

Data sensitivity labels, integrated with Microsoft Information Protection (MIP), allow users to classify sensitive data in Power BI reports, dashboards, datasets, and dataflows, and apply protection policies.

• Classify data based on sensitivity (e.g., Public, General, Confidential).
• Enforce protection actions (e.g., encryption, visual watermarks, prevent downloads).
• Integrated with Microsoft Information Protection.

Memory trick: Labels Limit Leaks.

A native Power BI visual type that enables users to execute R scripts within the report, using data from the Power BI model as input and rendering the script's visual output on the report canvas.

• Requires R installation on the local machine.
• Uses data from the Power BI model as input.
• Allows for custom R-based visualizations and statistical analysis.

Memory trick: R scripts can visualize, right in Power BI's sight, complex analysis, shining ever bright.

Deployment pipeline data source rules automate the modification of data source connection strings when content is deployed from one stage to another.

• Automate connection string changes
• Ensures correct environment targeting
• Configured per pipeline stage

Memory trick: The pipeline rules automatically route your data to the right place.

A configuration option in Power BI that, when enabled, causes selection in one visual to filter the data displayed in other visuals on the same report page, showing only the data related to the selection.

• One of three main interaction types (Filter, Highlight, None).
• Filters target visuals to show only relevant data.
• Configured via 'Edit interactions' mode.

Memory trick: Interact, filter, highlight, or nothing at all, Power BI visuals stand tall.

The ability of Power Query to translate transformation steps into the source database's native query language (e.g., SQL) and execute them on the source system, thereby reducing the amount of data transferred and leveraging source compute power.

• Optimizes performance for large datasets.
• Reduces network traffic by processing data at the source.
• Not all transformations can be folded; order of operations matters.

Memory trick: Fold the query, refresh in increments, cache your data, for best performance.

A Power Query transformation that allows you to find all occurrences of a specific value within a selected column and replace them with a new specified value.

• Used for data cleaning and standardization.
• Can replace text, numbers, nulls, or empty strings.
• Case-sensitive option available for text replacement.

Memory trick: Clean data, replace what's wrong, make it strong.

A Power Query transformation that extracts only the date component (year, month, day) from a date/time column and creates a new column containing just the date.

• Found under 'Add Column > Date'.
• Useful for reporting or grouping by date without time.
• Original column remains unchanged.

Memory trick: For dates and times, add or transform, parts you'll find, keep data warm.

Adding frequently used dimension attributes directly to the fact table to reduce relationship traversals and improve query performance, especially with large fact tables.

• Reduces join operations during query execution.
• Can increase fact table size and storage, but often improves query speed.
• Best for highly used filter/grouping columns from small dimensions.

Memory trick: Optimize performance by reducing relationship jumps.

The process of adding attributes from a dimension table directly into a fact table to reduce the need for joins and relationship traversals during query execution, thereby improving performance.

• Reduces query execution time by eliminating joins.
• Increases the size of the fact table.
• Best used for frequently filtered or grouped dimension attributes, especially with large fact tables.

Memory trick: For fast fact filters, put dimension details right in the fact.

Strategies to improve performance when a dimension column has many unique values. This often involves reducing the effective cardinality for analytical purposes, such as creating bins or ranges.

• High cardinality columns can increase model size and slow down queries.
• Binning or grouping into ranges reduces unique values for analytical use.
• Creating a new dimension table for ranges is a common solution.

Memory trick: For high cardinality, create ranges to simplify.

Selecting the smallest appropriate integer data type (e.g., Whole Number) for columns containing only whole numbers in Power Query to improve performance, reduce memory footprint, and ensure correct numerical operations in Power BI.

• Whole Number is efficient for integer values.
• Avoid Decimal or Fixed Decimal for pure integers.
• Choosing the right type reduces model size and query time.
• Important for columns like IDs, counts, or ranks.

Memory trick: Types define data's fate, choose wisely, don't hesitate.

A Power BI data model configuration that allows different tables to use different storage modes (Import, DirectQuery, Dual) to optimize performance, data freshness, and resource usage based on data source characteristics.

• Combines benefits of Import (speed) and DirectQuery (freshness).
• Requires careful consideration of data source types and update frequency.
• Tables in DirectQuery mode can impact query performance on the source system.

Memory trick: Import for speed, DirectQuery for fresh, Dual for both, a smart mesh.

A DAX pattern for identifying customer churn by comparing subscription end dates with current date and absence of future subscriptions.

• Requires identifying the latest subscription end date for each customer.
• Needs to check if this latest end date is in the past.
• Must verify that no new subscriptions exist after the latest end date.

Memory trick: Last End Past, No Next Start, Then Churn is Fast.

A calculated column in DAX adds a new column to a table with values computed at the row level, suitable for static, row-by-row calculations.

• Values are computed during data refresh and stored in the model.
• Ideal for static, row-level calculations that are frequently used.
• Increases model size but improves query performance for pre-calculated values.

Memory trick: Columns Compute Constantly, Measures Make Dynamic.

To enable scheduled data refresh for Power BI datasets connected to on-premises data sources, the data source must be registered with an installed On-premises Data Gateway and its credentials securely configured in the Power BI service.

• Required for on-premises data refresh (scheduled or on-demand).
• Gateway acts as a secure bridge between Power BI service and local data.
• Credentials are encrypted and stored in the Power BI service for the gateway.

Memory trick: Cloud needs a Bridge to Home data for Refresh.

Filtering data to show values for the current month, where 'current' is determined by the latest date in the dataset, often using DAX date intelligence functions.

• Uses MAX() to find the latest date.
• STARTOFMONTH() and ENDOFMONTH() define the month boundaries.
• DATESBETWEEN() applies the date filter.

Memory trick: Maximum date sets the stage, then boundaries define the month's age.

A measure calculating the percentage change in sales from the current month compared to the previous month.

• Requires calculating current month sales.
• Requires calculating previous month sales using time intelligence functions.
• Formula: (Current Month Sales - Previous Month Sales) / Previous Month Sales.

Memory trick: Current Minus Previous, Divide By Previous, Growth is Obvious.