Power BI Interview Questions and Answers

Power BI is a data visualization and business intelligence tool by Microsoft that helps in connecting, transforming and analyzing data. In interviews, questions often cover its core features like data modeling, DAX formulas, dashboards and reports as well as scenario-based problem-solving.

1. What is Power BI, Business Intelligence and its key features?

Power BI is a data visualization tool developed by Microsoft. It enables users to establish connections with diverse data sources, transform and manipulate data, generate interactive reports and dashboards and share insights with others.

• BI stands for Business Intelligence which refers to collecting, analyzing and delivering business data to support decision-making in organizations
• It enables users to visualize data and share that visualization with others.
• Users are able to browse and examine data from all sources (in a unified view).
• Users may scale across enterprises while benefiting from integrated governance and security.
• Users can see an output once it has been generated on any device that supports the Power BI application.
• Users can run queries on reports using basic English terms.

2. What is the difference between Power BI and Tableau?

Power BI and Tableau are popular Business Intelligence (BI) tools used for data visualization and analytics.

Power BI

• Developed by Microsoft.
• Easy to learn and use, especially for Excel users.
• Integrates seamlessly with Microsoft products such as Excel, Azure, SQL Server, and Microsoft 365.
• Uses DAX (Data Analysis Expressions) for calculations.
• More cost-effective for small and medium-sized organizations.
• Goal: Create business reports and dashboards with strong Microsoft ecosystem integration.

Tableau

• Developed by Salesforce.
• Offers advanced and highly interactive visualizations.
• Connects to a wide variety of data sources.
• Uses calculated fields and Tableau formulas for analysis.
• Preferred for complex data exploration and storytelling.
• Goal: Build rich, interactive visualizations and dashboards.

3. Differentiate between Power BI and Excel.

Power BI and Excel are Microsoft tools used for data analysis, but they serve different purposes.

Excel

• Spreadsheet application for data entry, calculations, and analysis.
• Best suited for small to medium-sized datasets.
• Uses formulas, PivotTables, and charts for analysis.
• Supports limited interactive dashboards.
• Commonly used for budgeting, reporting, and ad hoc analysis.
• Goal: Perform spreadsheet-based analysis and calculations.

Power BI

• Business Intelligence (BI) and data visualization tool.
• Designed to handle large datasets from multiple data sources.
• Uses Power Query for data transformation and DAX for calculations.
• Creates interactive dashboards and reports.
• Supports real-time data refresh and easy report sharing.
• Goal: Build interactive dashboards and business reports.

4. What are the different Power BI products/tools?

Power BI is not a single application — it's a suite of related tools:

• Power BI Desktop: A free Windows application used to connect to data, build the data model, write DAX and design reports. This is where report authoring happens.
• Power BI Service (app.powerbi.com): The cloud-based SaaS platform where reports are published, shared, scheduled to refresh and turned into dashboards. This is where report consumption and collaboration happens.
• Power BI Mobile: iOS/Android/Windows apps to view dashboards and reports on the go, with mobile-optimized layouts and alerts.
• Power BI Report Server: An on-premises alternative to the Power BI Service, used by organizations that cannot host data in the cloud.
• Power BI Report Builder: A standalone tool for creating paginated reports (pixel-perfect, print-ready reports — think invoices, statements).
• Power BI Embedded: An Azure service that lets developers embed Power BI reports/dashboards inside their own custom applications.

5. What are the different Power BI licensing tiers?

• Free: Create and view personal reports; cannot share content with others (except within Premium capacity).
• Pro: Per-user license required to publish, share and collaborate on workspaces/apps; most common license for individual contributors.
• Premium Per User (PPU): Adds Premium-only features (larger model size, paginated reports, AI features, more frequent refresh) on a per-user basis.
• Premium (capacity-based): Organization purchases dedicated capacity; anyone (even Free users) can view content without a Pro license.
• Fabric (F-SKU) capacity: Microsoft Fabric's unified capacity model, which now also powers Power BI Premium workloads alongside Fabric's other analytics engines (Lakehouse, Warehouse, Data Factory, etc.).

6. What is the difference between a Power BI Semantic Model (Dataset), Report, Dashboard and a Visual?

In Power BI, a Semantic Model (Dataset) stores and organizes data, a Report contains multiple interactive pages built from the dataset, a Dashboard is a single-page view displaying key insights from one or more reports, and a Visual is an individual chart, graph, or KPI displayed within a report or dashboard.

Semantic Model (Dataset)

• Collection of imported or connected data used for analysis.
• Contains tables, relationships, measures, and calculations.
• Serves as the data source for reports.
• Can be reused by multiple reports.
• Goal: Store and model data for analysis.

Report

• Collection of one or more interactive pages.
• Built using a semantic model (dataset).
• Contains multiple visuals such as charts, tables, and maps.
• Supports filtering, drill-down, and cross-filtering.
• Goal: Provide detailed data analysis.

Dashboard

• A single-page view of important business metrics.
• Created by pinning visuals from one or more reports.
• Provides a high-level summary for quick monitoring.
• Mainly used in the Power BI Service.
• Goal: Monitor KPIs and key insights at a glance.

Visual

• An individual chart, graph, table, map, KPI, or other visualization.
• Displays specific insights from the data.
• Used inside reports and dashboards.
• Goal: Represent data graphically.

7. How can you refresh data in Power BI?

Data can be refreshed in Power BI in the following manner:

• To manually update data in Power BI Desktop, click the "Refresh" button on the Home tab to update your report with the latest information from your sources.
• You can choose frequency like daily or weekly when you schedule automatic data refresh for published reports in the Power BI Service.
• In the Power BI Service, dataflows can be scheduled for refresh to keep shared datasets current.
• Data is always real-time for DirectQuery and Live Connection and doesn't need to be manually refreshed.
• To automate data refresh processes and guarantee data accuracy, use Power BI Gateway, Power Automate, APIs or PowerShell

8. What is a Power BI Gateway (On-premises Data Gateway) and why is it needed?

A gateway acts as a bridge between on-premises data sources (SQL Server, files on a local network, SSAS, etc.) and Power BI Service in the cloud, so scheduled refreshes and DirectQuery/Live Connection can securely reach data that isn't publicly accessible on the internet.

• On-premises Data Gateway (Standard mode): Supports multiple users and data sources; commonly used for organization-wide connectivity.
• On-premises Data Gateway (Personal mode): Single-user, only supports scheduled refresh for import-mode datasets (no DirectQuery/Live Connection, no sharing across users).
• The gateway encrypts credentials and never stores raw data — it only relays queries and results between the Service and the source.

9. What are the major components of Power BI?

There are five different components of Power BI.

• Power Query: Connects to, cleans and transforms raw data before it's loaded into the model (the ETL layer).
• Power Pivot: Takes the loaded data and builds the data model — relationships, calculated columns, measures and DAX calculations.
• Power Q&A: Makes it possible for users to interact with reports using simple English language.
• Power View: Lets users create interactive charts, graphs, maps and other visuals.
• Power Map: Enables the processing and visualization of accurate geographic locations in datasets, including 3D and time-based views.

10. What is Power Query in Power BI?

Power Query in Power BI is a data connection and transformation tool that helps you import, clean and reshape data before using it for analysis and reporting. It allows users to connect to multiple data sources, apply transformations and prepare the data without needing complex coding.

• Used for data extraction, transformation and loading (ETL).
• Provides a user-friendly editor to clean and format data.
• Supports connections to databases, Excel, cloud services and APIs.
• Uses the M language in the background for transformations.

11. What is Query Folding in Power Query?

Query folding is when Power Query translates the transformation steps you apply in the editor (filter, sort, group, etc.) back into a single native query (e.g., SQL) that is pushed down and executed on the source database, instead of pulling all raw data into Power BI first and transforming it locally.

• Improves performance dramatically for large relational sources, since filtering/aggregation happens on the source server.
• Only works for sources that support it (SQL Server, most databases); it typically breaks for flat files (Excel/CSV) or after certain steps like custom M functions, merging with a non-foldable source, or adding an index column.
• You can check whether a step still folds by right-clicking it in the Applied Steps pane and checking if "View Native Query" is available.

12. What are Parameters in Power Query?

Parameters in Power Query are reusable, named values that can be referenced across queries — for example a FilePath, StartDate, or EnvironmentName. They make queries dynamic and easy to reconfigure without editing the M code of every query individually.

• Common use case: switching a connection between Dev/Test/Prod environments by changing one parameter value.
• Can be used to build a Function in Power Query that's applied to every file in a folder (a common pattern for combining many similarly-structured files).

13. What are different data sources in Power BI?

Power BI supports a wide variety of data sources that you can connect to for building reports and dashboards. These can be broadly grouped as:

1. File Sources: Excel (.xlsx, .xlsm), CSV (.csv), XML, JSON, PDF
2. Database Sources: SQL Server, Oracle Database, MySQL, PostgreSQL
3. Online Services: Microsoft Azure, Power BI Semantic Models, Dynamics 365, Google Analytics, Salesforce
4. Cloud & Big Data Sources: Hadoop (HDFS), Spark, Google BigQuery, Amazon Athena, Azure Blob Storage, Microsoft Fabric Lakehouse/Warehouse
5. Other Sources: Web (URL data), R Script / Python Script, APIs (through custom connectors)

14. What is data modelling in Power BI and how do relationships work within it?

Data modelling in Power BI is the process of structuring and organizing data so that it can be used effectively for analysis and reporting. It involves:

• Creating tables (from data sources).
• Defining calculated columns, measures and hierarchies.
• Establishing relationships between tables.

A key part of data modelling is creating relationships between two tables, which connect data using common fields like CustomerID or ProductID. These relationships can be:

• One-to-Many (1:*): One record relates to multiple records like One Customer → Many Orders.
• Many-to-Many (:); Multiple records relate to multiple records like Students ↔ Courses.
• One-to-One (1:1): When each row in one table matches exactly one row in another like Employee → Employee ID.

15. What is Star Schema vs Snowflake Schema? Why does Power BI recommend a Star Schema?

Star Schema and Snowflake Schema are data modeling techniques used in data warehouses.

Star Schema

• Consists of a central Fact Table surrounded by Dimension Tables.
• Dimension tables are denormalized.
• Requires fewer joins, resulting in faster queries.
• Simpler to understand and maintain.
• Best suited for reporting and BI tools like Power BI.
• Goal: Optimize query performance and simplify data analysis.

Snowflake Schema

• Consists of a Fact Table with normalized Dimension Tables.
• Dimension tables are split into multiple related tables.
• Requires more joins during queries.
• Reduces data redundancy but increases complexity.
• Commonly used where storage optimization is important.
• Goal: Reduce redundancy and improve data consistency.

16. What is Cross-Filter Direction (Single vs Bidirectional) in relationships?

Cross-filter direction controls how filters flow across a relationship between two tables:

• Single direction: Filtering the "one" side of a 1:* relationship filters the "many" side (e.g., filtering Date filters Sales), but not the other way around. This is the default and recommended setting for star schemas.
• Bidirectional (Both): Filters propagate in both directions. Useful for many-to-many relationships or specific scenarios, but can cause performance issues and ambiguous filter paths if overused, so it should be applied selectively rather than by default.

17. What is the difference between Calculated Columns, Calculated Tables and Measures?

Calculated Columns, Calculated Tables, and Measures are DAX objects used for data modeling and analysis in Power BI.

Calculated Column

• Creates a new column in an existing table.
• Calculated row by row during data refresh.
• Values are stored in the data model.
• Can be used in rows, columns, filters, slicers, and relationships.
• Goal: Add derived data to each row.

Calculated Table

• Creates an entirely new table using DAX.
• Calculated during data refresh.
• Stored in the data model.
• Can be related to other tables.
• Goal: Create new tables for modeling and analysis.

Measure

• Performs dynamic calculations based on the current filter context.
• Calculated at query time (not stored).
• Returns a single aggregated value.
• Used in visuals for KPIs, totals, averages, and percentages.
• Goal: Perform dynamic aggregations and business calculations.

18. What are the different ways to create a table in Power BI?

There are three main ways to bring a table into a Power BI model:

1. Load from a data source (the most common way) — connect via Get Data / Power Query to a file, database, API, etc.
2. Enter Data manually — type a small table directly into Power BI Desktop without any external source (see Q19 below).
3. Create a Calculated Table using DAX — build a new table inside the model from existing tables/expressions using the Modeling tab → New Table (see Q20 below).

19. What is the "Enter Data" feature in Power BI, and when would you use it?

Enter Data (Home tab → Enter Data) lets you manually type or paste a small static table directly into Power BI Desktop, without connecting to any external file or database. Power BI creates it as a regular query in Power Query behind the scenes, so it can still be transformed like any other table.

Typical use cases:

• Creating a small lookup/mapping table (e.g., a Region-to-Manager mapping, or bucket labels like "Low/Medium/High").
• Quickly prototyping a table structure before wiring it up to a real data source.
• Adding constant reference values (targets, thresholds) that don't come from any system.

Steps: Home tab → Enter Data → type column names and values in the grid (or paste from Excel/clipboard) → name the table → Load.

20. How do you create a Calculated Table in Power BI using DAX?

A Calculated Table is created from the Modeling tab → New Table, then writing a DAX expression that returns a table (not a scalar value). Common table-returning DAX functions used for this:

• DATATABLE(...): Manually defines a small static table with explicit column names, types and values, directly in DAX.
• UNION(Table1, Table2): Stacks rows from two tables with matching columns (DAX equivalent of Append Queries).
• SUMMARIZE(Table, GroupByColumn, "NewCol", Expression): Creates a grouped/aggregated table.
• DISTINCT(Column) / VALUES(Column): Returns a one-column table of unique values.
• FILTER(Table, Condition): Returns a filtered subset of a table's rows.
• GENERATESERIES(Start, End, Step): Generates a table of sequential numeric values — often used to build a numbers or bucket table.

Example: a calculated table of unique regions with a running total column:

RegionSummary =

SUMMARIZE( Sales, Sales[Region], "Total Sales", SUM(Sales[Amount]))

21. How do you create a Date/Calendar table in Power BI using DAX?

Use Modeling tab → New Table, then either CALENDAR or CALENDARAUTO:

• CALENDAR(StartDate, EndDate) — generates a continuous list of dates between two explicitly specified dates.

DateTable = CALENDAR(DATE(2020,1,1), DATE(2025,12,31))

• CALENDARAUTO([FiscalYearEndMonth]) — automatically scans every date column in the model and generates a continuous date range covering the earliest to latest date found, without hardcoding start/end dates.

DateTable = CALENDARAUTO()

After creating the base date column, add supporting calculated columns (Year, Month, Month Name, Quarter, Week Day, etc.), for example:

Year = YEAR(DateTable[Date])

Month Name = FORMAT(DateTable[Date], "MMMM")

Quarter = "Q" & FORMAT(DateTable[Date], "Q")

Finally, go to Modeling tab → Mark as Date Table, and select the date column — this is required for Time Intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR to work correctly.

22. What is the difference between the "New Table," "New Column," and "New Measure" buttons in Power BI Desktop?

All three are on the Modeling ribbon and open the same DAX formula bar, but they create fundamentally different objects:

• New Table: DAX expression must return an entire table; creates a new calculated table in the model (uses functions like SUMMARIZE, UNION, CALENDAR, FILTER).
• New Column: DAX expression is evaluated row by row within an existing table and returns a single value per row, adding a new column to that table.
• New Measure: DAX expression returns a single aggregated value that's evaluated dynamically based on filter context; not stored anywhere in the model.

23. How do you create a table in Power Query without connecting to any external data source?

In the Power Query Editor, go to Home tab → New Source → Blank Query, then either:

• Build the table entirely with M code in the Advanced Editor, e.g.:

= #table(

{"Region", "Target"},

{{"North", 50000}, {"South", 45000}, {"East", 40000},{"West", 42000} } )

• Or simply use Enter Data , which is the friendlier, no-code equivalent of the same thing.

24. What is Power Pivot in Power BI?

Power Pivot in Power BI is a data modelling component that lets you build relationships between tables, create calculations and manage large datasets efficiently. It uses DAX (Data Analysis Expressions) to define calculated columns, tables and measures making it possible to perform advanced analysis beyond simple visualizations.

• Helps in creating relationships between multiple tables.
• Allows complex calculations using DAX.
• Optimized for handling large volumes of data.
• Works as the engine behind data models in Power BI.

25. Difference Between Power View and Power Map?

Power View and Power Map are Microsoft Excel data visualization tools.

Power View

• Creates interactive charts, tables, matrices, and dashboards.
• Used for general business reporting and data exploration.
• Supports filtering, slicing, and drill-down analysis.
• Best for analyzing non-geographical data.
• Goal: Build interactive reports and dashboards.

Power Map (3D Maps)

• Visualizes geographical and location-based data on a 3D globe or map.
• Supports animated time-based data visualization.
• Displays data using bubbles, heat maps, and region maps.
• Best for analyzing spatial and geographic trends.
• Goal: Visualize geographic data and location-based insights.

26. What is Power Q&A in Power BI?

Power Q&A in Power BI is a feature that lets users ask questions about their data in natural language and get instant answers in the form of charts or visuals. Instead of writing queries, you can type (or even speak) questions like “Total sales by region” or “Top 5 products by revenue” and Power BI automatically generates the appropriate visualization.

• Uses natural language processing (NLP).
• Suggests relevant questions as you type.
• Provides instant, interactive visuals.
• Makes data exploration simple for non-technical users.

27. What are the different ways to filter the data in Power Bi?

Data can be filtered in Power BI using various filters. There are:

1. Visual-Level Filters

• Apply a filter to a single visual (chart, table or card).
• Only affects that particular visual on the report page.

2. Page-Level Filters

• Apply a filter to all visuals on a specific report page.
• Useful for focusing the page on a specific subset of data.

3. Report-Level Filters

• Apply a filter to all pages in the report.
• Ensures consistency of filtered data across the entire report.

4. Drillthrough Filters

• Allows users to right-click on a visual and navigate to another page showing detailed filtered data.
• For example click on a country in a map to see sales details for that country.

5. Slicers

• Interactive visual filters that allow users to select values dynamically.
• Can be single-select or multi-select.

6. Top N Filters

• Show only the top or bottom N items based on a measure.
• For example top 10 products by revenue.

7. Cross-Filtering / Cross-Highlighting

• Clicking on one visual automatically filters or highlights data in other visuals on the page.

8. Advanced Filters

• Supports logical conditions like greater than, less than, contains or custom formulas.

28. Difference between Drill Down and Drill Through in Power BI?

Drill Down and Drill Through are navigation features in Power BI that help users explore data at different levels of detail.

Drill Down

• Navigates through hierarchical levels in the same visual.
• Used to move from summary data to more detailed data.
• Stays on the same report page.
• Requires a hierarchy (e.g., Year → Quarter → Month → Day).
• Goal: Explore data within a visual.

Drill Through

• Navigates to a different report page.
• Passes the selected value as a filter to the destination page.
• Used to view detailed information for a specific record or category.
• Requires a Drill Through page configured with drill-through fields.
• Goal: Analyze detailed data on a separate page.

29. Difference between Slicers vs Filters?

Slicers and Filters are both used to filter data in Power BI, but they differ in how users interact with them.

Slicers

• Interactive visual controls placed on the report.
• Allow users to filter data by selecting values.
• Visible on the report canvas.
• Support dropdowns, lists, date ranges, and buttons.
• Best for enabling user-driven filtering.
• Goal: Provide an easy, interactive way to filter report data.

Filters

• Applied through the Filter pane.
• Can be applied at the visual, page, or report level.
• Usually managed by report developers.
• Not displayed as visuals on the report canvas.
• Best for controlling which data is displayed.
• Goal: Restrict data shown in visuals or reports.

30. What are the different types of visualizations in Power BI?

Power BI provides a variety of visualization types to represent data effectively. These include:

1. Bar and Column Charts: Vertical and horizontal bars to compare data across categories.
2. Line and Area Charts: Show trends over time or continuous data.
3. Pie and Donut Charts: Represent proportions or percentages of a whole.
4. Card and KPI Visuals: Display key metrics or single values prominently.
5. Tables and Matrices: Show detailed data in rows and columns. Matrices support hierarchical data.
6. Scatter and Bubble Charts: Display relationships and correlations between numeric values.
7. Maps: Geographical visualizations including basic maps, shape maps and ArcGIS maps.
8. Funnel Charts: Represent stages in a process like sales or conversions.
9. Gauge and Dial Charts: Show progress against a target or goal.
10. Waterfall Charts: Illustrate cumulative effects of sequential positive and negative values.
11. Decomposition Tree: Break down a measure across multiple dimensions interactively.
12. Ribbon Charts: Show ranking changes of categories over time.
13. Custom Visuals: Additional visuals imported from the Power BI marketplace for specialized needs.

31. What is DAX? What are the benefits of using variables in DAX?

DAX or Data Analysis Expressions is a formula language used in Power BI, Power Pivot and Analysis Services to perform data calculations and analysis. It allows you to create calculated columns, measures and tables to enhance your data model.

• Used for aggregations, calculations and data manipulation.
• Works with tables and relationships in the data model.
• Similar to Excel formulas but designed for relational data.

DAX Syntax:

MeasureName = VAR x = <expression>
RETURN x

DAX Example:

TotalSalesWithTax = VAR SalesAmount = SUM(Sales[Amount])
RETURN SalesAmount * 1.1

Benefits of Using Variables in DAX

• Improves readability of complex formulas.
• Enhances performance by calculating values once and reusing them.
• Simplifies complex calculations.
• Makes debugging easier by testing parts of the formula individually.

32. What are the three fundamental concepts of DAX?

Three fundamental concepts of DAX are as follows:

1. Row Context

• Refers to calculations performed row by row in a table.
• Example: Calculating Profit = Sales[Amount] – Sales[Cost] for each row.

2. Filter Context

• Refers to the subset of data applied to a calculation using filters, slicers or measures.
• Example: Total Sales for a specific Region or Year.

3. Context Transition

• Occurs when row context is converted into filter context inside a measure, typically with functions like CALCULATE().
• Example: Using CALCULATE(SUM(Sales[Amount]), Region="India") in a measure.

33. What are the most common DAX functions used?

Some of the most commonly used DAX functions are listed below:

• Aggregation Functions: SUM, MIN, MAX, AVERAGE, COUNTROWS, DISTINCTCOUNT
• Information Functions: ISBLANK, ISFILTERED, ISCROSSFILTERED
• Statistical Functions: GEOMEAN, MEDIAN
• Logical Functions: IF, AND, OR, SWITCH
• Date & Time Functions: DATEDIFF, DATEVALUE
• Filter Functions: VALUES, ALL, FILTER, CALCULATE, TOPN
• Other Functions: UNION, INTERSECT, EXCEPT, NATURALINNERJOIN, NATURALLEFTOUTERJOIN, SUMMARIZECOLUMNS, ISEMPTY, VAR

34. What are Time Intelligence functions in DAX?

Time Intelligence functions let you perform calculations across time periods (year, quarter, month) — provided you have a proper Date table marked as the model's date table.

Example: Year-to-Date sales

Sales YTD = TOTALYTD(SUM(Sales[Amount]), Date[Date])

35. What are Iterator (X) functions in DAX?

Iterator functions (functions ending in X) evaluate an expression row by row over a table and then aggregate the result, instead of aggregating a single column directly. This makes them essential when a calculation needs values from multiple columns combined per row.

Total Revenue = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])

Common iterator functions: SUMX, AVERAGEX, MAXX, MINX, COUNTX, RANKX, FILTER (used inside iterators).

• SUM(Sales[Quantity] * Sales[UnitPrice]) would actually error out, because SUM only accepts a single column — this is exactly why SUMX exists.

36. Difference Between COUNTROWS and DISTINCTCOUNT in DAX

COUNTROWS() and DISTINCTCOUNT() are DAX functions used for counting data, but they count different things.

COUNTROWS()

• Counts the total number of rows in a table.
• Accepts a table as input.
• Includes duplicate rows.
• Commonly used to count records after applying filters.
• Goal: Count all rows in a table.

DISTINCTCOUNT()

• Counts the unique values in a column.
• Accepts a column as input.
• Ignores duplicate values.
• Commonly used to count unique customers, products, orders, etc.
• Goal: Count distinct values in a column.

37. What is the CALCULATE function in DAX?

The CALCULATE function in DAX is used to modify the filter context of a calculation and evaluate an expression under those filters. It is one of the most important DAX functions because it allows dynamic calculations based on specific conditions.

• Changes the filter context for a measure or calculation.
• Can apply multiple filters at once.
• Often used with aggregations like SUM, AVERAGE, COUNT, etc.

Syntax:

MeasureName = CALCULATE(<expression>, <filter1>, <filter2>, …)

Example:

TotalSalesIndia = CALCULATE(SUM(Sales[Amount]), Sales[Country] = "India")

38. Difference between ALL, ALLEXCEPT and ALLSELECTED in DAX?

ALL(), ALLEXCEPT(), and ALLSELECTED() are DAX functions used to modify the filter context.

ALL()

• Removes all filters from a table or column.
• Returns all rows regardless of filters.
• Commonly used to calculate grand totals or percentages of the overall total.
• Goal: Ignore all filters.

ALLEXCEPT()

• Removes all filters except those on specified column(s).
• Preserves the selected grouping columns.
• Commonly used for grouped calculations.
• Goal: Keep only specific filters.

ALLSELECTED()

• Removes filters applied within the current visual but keeps user selections (e.g., slicers, page/report filters).
• Useful for calculating visual totals.
• Reflects only the data currently selected by the user.
• Goal: Respect user selections while ignoring visual-level filters.

39. What is Filter Context?

Filter Context is the set of filters applied to a calculation or measure that determines which data is included in the result. It is created automatically by slicers, filters, rows, columns or measures in a report.

• Determines the subset of data for calculations.
• Can be applied manually using functions like CALCULATE or automatically via report visuals.
• Essential for dynamic and accurate measures.

For example, If you have a measure Total Sales = SUM(Sales[Amount]) and a page filter Region = "India", the filter context ensures that Total Sales shows only the sales from India.

40. What are custom visualizations in Power BI?

• Power BI allows users to add customized visual elements to their reports and dashboards.
• These custom visuals, which can be created by users or third-party developers, offer unique ways to present and analyze data beyond the standard visuals provided by Power BI.
• By exploring the Custom Visuals Gallery in Power BI, users can discover and integrate these custom visuals into their reports to enhance the presentation and analysis of data.
• These custom visuals are especially useful when industries require specialized chart types or when the standard visuals don't meet specific visualization requirements.
• Overall, custom visuals in Power BI enable users to create more engaging and tailored reports and dashboards.

41. Difference Between Row Context and Filter Context in DAX

Row Context and Filter Context are two fundamental concepts in DAX that determine how calculations are evaluated.

Row Context

• Refers to the current row being evaluated.
• Used in calculated columns and iterator functions like SUMX(), AVERAGEX(), etc.
• Calculates values row by row.
• Does not automatically consider report filters.
• Goal: Perform row-level calculations.

Filter Context

• Represents the filters applied to the data.
• Created by slicers, filters, rows/columns in visuals, or DAX functions like CALCULATE().
• Used mainly in measures.
• Calculates results based on the filtered subset of data.
• Goal: Perform dynamic calculations based on the current filters.

42. What are Measures in DAX?

Measures are calculations in Power BI, Power Pivot or Analysis Services that compute results dynamically based on the current filter context. Unlike columns, they aren’t stored in the table, instead they are evaluated dynamically whenever you interact with visuals, slicers or filters in your report.

• Created using DAX formulas.
• React to filters, slicers and other visuals automatically.
• Commonly used for sum, average, count, percentage or custom calculations.
• Stored in the data model, not in individual rows.

Example:

Total Sales = SUM(Sales[Amount])

It shows the total sales dynamically based on applied filters like region or year.

43. What are different DAX Aggregate Funtions?

Common DAX Aggregate Functions:

• SUM: Adds up all numeric values in a column. For example: SUM(Sales[Amount])
• AVERAGE: Calculates the average of numeric values in a column. For example: AVERAGE(Sales[Amount])
• MIN: Returns the smallest value in a column. For example: MIN(Sales[Amount])
• MAX: Returns the largest value in a column. For example: MAX(Sales[Amount])
• COUNT: Counts the number of non-blank values in a column. For example: COUNT(Sales[OrderID])
• COUNTA: Counts all non-empty values including text, numbers and dates. For example: COUNTA(Sales[CustomerName])
• COUNTROWS:Counts the number of rows in a table or table expression. For example: COUNTROWS(Sales)
• DISTINCTCOUNT: Counts unique values in a column. For example: DISTINCTCOUNT(Sales[CustomerID])
• PRODUCT: Returns the product of all values in a column. For example: PRODUCT(Sales[Quantity])
• MEDIAN: Returns the median value of a column. For example: MEDIAN(Sales[Amount])
• SUMX: Row-wise sum then aggregate the results. For example: SUMX(Sales, Sales[Quantity] * Sales[Price])

44. What are the different views available in Power BI Desktop?

Power BI has three distinct views, each serving a unique purpose:

1. Report View

• Used to create and design reports and dashboards.
• Allows you to drag visuals, charts and slicers in canvas.

2. Data View

• Lets you view, explore and manage your data in tables.
• You can create calculated columns, measures and explore row-level data.

3. Model View

• Used to define relationships between tables and manage the data model.
• Allows you to see table connections, create relationships and organize the model visually.

45. What is a Conditional Column in Power BI?

A Conditional Column in Power BI is a column created based on a condition or rule applied to existing columns. It allows you to categorize or transform data without writing DAX formulas.

• Created in Power Query Editor.
• Values in the new column depend on conditions applied to other columns.
• Useful for categorizing, grouping or flagging data.

Syntax Example:

if [SalesAmount] > 1000 then "High"
else if [SalesAmount] >= 500 then "Medium"
else "Low"

Here:

• If SalesAmount > 1000 → High
• If SalesAmount between 500–1000 → Medium
• If SalesAmount < 500 → Low

46. What are Append Queries in Power BI?

Append Queries in Power BI is a feature in Power Query Editor that allows you to combine rows from two or more tables into a single table. It’s like stacking tables on top of each other.

• Used when tables have similar columns.
• Helps consolidate data from multiple sources.
• Can append two tables at a time or multiple tables.

Syntax Example:

Table.Combine({Table1, Table2})

Here:

• It combines Table1 and Table2 into a single table.
• Resulting table contains all rows from both tables.

47. Difference between Append Queries and Merge Queries?

Append Queries and Merge Queries are Power Query operations used to combine data, but they do so differently.

Append Queries

• Combines two or more tables vertically by adding rows.
• Tables should have the same or similar columns.
• Similar to the SQL UNION operation.
• Used to combine data from multiple files or periods.
• Goal: Increase the number of rows.

Merge Queries

• Combines two tables horizontally based on matching columns.
• Requires a common key between tables.
• Similar to SQL JOIN operations (Inner, Left, Right, Full, etc.).
• Used to enrich one table with related data from another.
• Goal: Add related columns to an existing table.

48. How to Remove Null and Duplicate Values in Power BI

1. Removing Null Values:

• Go to Power Query Editor.
• Select the column(s) where you want to remove nulls.
• From the Home tab → Remove Rows → Remove Blank Rows.

Example: If a CustomerID column has null values, they will be removed.

2. Removing Duplicate Values:

• In Power Query Editor, select the column(s) where duplicates may exist.
• From the Home tab → Remove Rows → Remove Duplicates.

Example: If the Sales table has duplicate OrderID, selecting OrderID and removing duplicates will keep only unique orders.

49. What is KPI in Power BI?

A KPI (Key Performance Indicator) in Power BI is a visual used to track progress toward a specific goal or business target. It helps measure performance by comparing actual values against a target value.

• KPI visuals show status (current value), target (goal) and trend (progress over time).
• Useful for monitoring metrics like sales vs. target, revenue growth, customer satisfaction, etc.
• Created using measures in DAX (for actual and target values).

Example: If you want to track Sales Performance:

• Actual Sales = SUM(Sales[Amount])
• Target Sales = 1,00,000
• KPI visual shows whether actual sales are below, meeting or above the target.

50. What are Add-ins in Power BI?

Add-ins in Power BI are extra tools or extensions that you can integrate with Power BI to extend its functionality. They allow users to bring in advanced visuals, connect with other applications or use specialized features that aren’t available by default.

Types of Add-ins:

1. Custom Visuals: Imported from AppSource or developed by users. It can include heat maps, word clouds, etc.
2. Office Add-ins: Power BI add-ins available in tools like Excel and PowerPoint to embed or analyze Power BI data.
3. Third-party Add-ins: External connectors and tools that integrate Power BI with services like Salesforce, Adobe Analytics, etc.

Example:

• Embedding Power BI reports in PowerPoint using the Power BI add-in.
• Importing a Hierarchy Slicer visual from AppSource into Power BI Desktop.

51. What is Row-Level Security (RLS) in Power BI? Explain static and dynamic RLS.

Row-Level Security restricts what data individual users can see within the same report, based on their identity, without needing to build separate reports per user.

• Static RLS: Roles are defined with hardcoded filter conditions (e.g., a "India Manager" role filters Region = "India"). Users are then manually assigned to a role in the Power BI Service.

[Region] = "India"

• Dynamic RLS: The filter condition uses a function like USERPRINCIPALNAME() to compare the logged-in user's email/UPN against a mapping table (e.g., a UserRegionMapping table), so one single role automatically shows each user only their own data — no need to create a role per region.

[Email] = USERPRINCIPALNAME()

Steps to implement:

1. In Power BI Desktop → Modeling tab → Manage Roles → create a new role and define the DAX filter expression.
2. Publish the report to the Power BI Service.
3. In the workspace → dataset settings → Security → assign users/groups to the role.

52. What are Bookmarks in Power BI?

• Bookmarks capture the current state of a report page — filter selections, slicer values, sort order, visibility of visuals — so you can return to that exact view with one click.
• They're commonly used to build navigation buttons, guided "storytelling" walkthroughs, and toggling between different visual states (e.g., switching a chart between "By Region" and "By Product" without adding a second page).

53. What is a Hierarchy in Power BI?

A hierarchy groups related fields into levels that can be drilled through together — for example a Date hierarchy (Year → Quarter → Month → Day) or a Geography hierarchy (Country → State → City). Hierarchies let a single visual support drill down/up interactions instead of building separate visuals for each level.

54. What is the difference between Import, DirectQuery and Live Connection modes?

Import, DirectQuery, and Live Connection are data connectivity modes in Power BI.

Import Mode

• Imports data into the Power BI model.
• Provides the fastest query performance.
• Supports all DAX and data modeling features.
• Data must be refreshed to reflect source changes.
• Goal: Fast reporting with imported data.

DirectQuery Mode

• Does not import data into Power BI.
• Sends queries directly to the source database in real time.
• Always displays the latest data.
• Performance depends on the source database.
• Some DAX and modeling features are limited.
• Goal: Analyze live data without storing it.

Live Connection Mode

• Connects to an existing Power BI Semantic Model or SQL Server Analysis Services (SSAS).
• Uses the existing data model and measures.
• Data modeling in Power BI is limited because the model is managed externally.
• Always displays the latest data from the connected model.
• Goal: Reuse a centralized semantic model.

55. What is a Dataflow in Power BI?

• A dataflow is a reusable, cloud-hosted ETL process built with Power Query Online, run and stored in the Power BI Service independent of any single report.
• It lets multiple reports/datasets consume the same cleaned, transformed data instead of every report author rebuilding the same Power Query steps from scratch — improving consistency and reducing duplicate transformation logic across an organization.

56. What is a Composite Model in Power BI?

• A composite model combines multiple storage modes within a single semantic model — for example, using Import mode for smaller, frequently-reused dimension tables and DirectQuery for a very large fact table.
• This gives flexibility to balance performance and data freshness table by table, instead of being forced to pick one storage mode for the entire model.

57. Find Average Sales per Customer.

We can write dax query like:

Avg Sales per Customer =
DIVIDE(
SUM(Sales[Amount]),
DISTINCTCOUNT(Sales[CustomerID])
)

58. Find Distinct number of Products sold.

We can write dax query like:

Unique Products = DISTINCTCOUNT(Sales[ProductID])

59. Find Total Employees who joined after 2020.

We can write dax query like:

Employees_After2020 =
CALCULATE(
COUNTROWS(Employee),
Employee[JoiningYear] > 2020
)

60. Find Year-over-Year (YoY) Growth in Sales.

We can write dax query like:

YoY Sales Growth =
DIVIDE(
[Total Sales] - CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Date[Date])),
CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Date[Date]))
)

61. Find Month-over-Month (MoM) Growth in Sales.

Month-over-Month (MoM) Growth measures the percentage change in sales compared to the previous month. It is commonly used to analyze short-term business performance and identify sales trends.

MoM Growth % =

DIVIDE( [Total Sales] - [Previous Month Sales], [Previous Month Sales], 0 )

62. Find % of Total Sales by Region.

We can write dax query like:

Sales % by Region =
DIVIDE(
SUM(Sales[Amount]),
CALCULATE(SUM(Sales[Amount]), ALL(Sales[Region]))
)

63. Find Top 5 Products by Sales in 2023.

Use a Top N filter in the visual:

• Add Product and Total Sales
• Apply Top N = 5 by Sales OR in DAX:

Top5 Products =
TOPN(5, SUMMARIZE(Sales, Sales[Product], "Sales", SUM(Sales[Amount])), [Sales], DESC)

64. Find the Second Highest Sales Value using DAX.

The second highest sales value can be found by first identifying the maximum sales value, excluding it, and then finding the maximum of the remaining values.

Second Highest Sales =

CALCULATE(MAX(Sales[Amount]), FILTER(ALL(Sales), Sales[Amount] < CALCULATE(MAX(Sales[Amount]), ALL(Sales))))

65. Rank Products by Sales using RANKX.

The RANKX() function is used to rank values based on an expression. It is commonly used to rank products, customers, or regions based on measures such as sales, profit, or quantity.

Product Sales Rank =

RANKX(ALL(Sales[Product]), CALCULATE(SUM(Sales[Amount])), ,DESC)

66. Your report is very slow because of large data. What steps can you take?

• Reduce data using filters before loading.
• Use Import Mode instead of DirectQuery.
• Remove unnecessary columns.
• Use Aggregations for summary tables.

67. How to ask Power BI “Show me total sales in 2023” in natural language. How will you do this?

We can use Power Q&A by typing the question directly into the Q&A visual.

Syntax:

total sales in 2023

68. If we have two tables: Customers and Orders. How do you connect them?

Create a One-to-Many relationship by CustomerID in Customers → CustomerID in Orders.

Syntax:

Customers[CustomerID] 1 → * Orders[CustomerID]

69. How can you merge two datasets like Online Sales and Store Sales into one?

Use Append Queries in Power Query.

Syntax:

Combined Sales = Table.Combine({OnlineSales, StoreSales})

70. How do you combine Customer details with Order details that share a CustomerID, adding columns instead of rows?

Use Merge Queries (a Left Outer Join keeps every row from Orders and pulls in matching customer columns):

1. In Power Query Editor, select the Orders query.
2. Home tab → Merge Queries.
3. Choose Customers as the table to merge with, and select CustomerID as the matching column in both tables.
4. Set Join Kind to Left Outer (keeps all Orders rows, adds matches from Customers).
5. Expand the new Customers column to pull in the specific fields you need (e.g., CustomerName, Region).

= Table.NestedJoin(Orders, {"CustomerID"}, Customers, {"CustomerID"}, "CustomerDetails", JoinKind.LeftOuter)

71. You want to add a new column that says "Pass" if marks > 40 else "Fail". How will you do this?

Use Conditional Column in Power Query with rule: If Marks > 40 → Pass, Else → Fail.

Syntax:

Result = if [Marks] > 40 then "Pass" else "Fail"

72. How can sales data be summarized by Region and Month. How do you do this?

Use a Matrix visualization with Region as rows, Month as columns and Sales as values.

Syntax:

Rows = Sales[Region]
Columns = Sales[Month]
Values = SUM(Sales[Amount])

73. How to analyze trends in Sales over time. Which visualization will you use?

Use a Line Chart with Date on the X-axis and Sales on the Y-axis.

74. How do you build a KPI showing whether actual sales met the target?

Create Actual Sales and Target Sales measures, then use a KPI visual to compare:

Syntax:

Actual Sales = SUM(Sales[Amount])
Target Sales = SUM(Targets[TargetAmount])

75. You need to restrict data visibility so that regional managers only see data for their own region. How will you achieve this?

This is Row-Level Security (RLS) in practice — see the dedicated RLS question earlier in this guide for the concept:

1. Define roles in Power BI Desktop (e.g., [Region] = USERPRINCIPALNAME() mapped through a user-to-region table for dynamic RLS).
2. Publish the report to Power BI Service.
3. Assign users/groups to the roles under the dataset's Security settings.

76. How to create a dynamic title for your report page that changes based on the selected year.

Use a Card visual with a measure:

DynamicTitle = "Sales Report for " & SELECTEDVALUE(Date[Year])

77. You want to combine multiple date tables into one master date table. How do you do it?

• In Power Query, use Append Queries to combine all date tables.
• Remove duplicates on the date column.

MasterDate = Table.Distinct(Table.Combine({Date1, Date2, Date3}))

78. How to show a trend of cumulative sales over the year.

Create a Running Total measure in DAX:

Cumulative Sales = CALCULATE(SUM(Sales[Amount]), FILTER(ALL(Date), Date[Date] <= MAX(Date[Date])))

79. How do you implement Incremental Refresh in Power BI? (Steps)

Incremental refresh only reloads new/changed data instead of the entire table on every refresh, which is critical for large fact tables.

1. In Power Query, filter the table using two parameters named exactly RangeStart and RangeEnd (Date/Time type) on your date column.
2. In Power BI Desktop → right-click the table in the Fields pane → Incremental refresh.
3. Toggle it on, and set the policy: e.g., "Archive data starting X years before refresh date" and "Incrementally refresh data starting X days before refresh date."
4. Optionally enable "Detect data changes" (using a LastModified-style column) so only truly changed rows are reprocessed.
5. Publish to the Power BI Service (Premium/PPU/Fabric capacity required for the incremental refresh engine to actually partition the table) — Power BI Desktop will only load a small sample locally; the full partitioning happens after publishing.

80. How do you publish and share a Power BI report via the Power BI Service?

1. In the Power BI Service, open the workspace and confirm the report and its semantic model appear.
2. Set up scheduled refresh on the semantic model (Settings → Scheduled refresh) if the data source isn't DirectQuery/Live.
3. To share with individuals: use Share on the report and grant view/edit access, or add users to the workspace with the appropriate role (Viewer, Member, Contributor, Admin).
4. To distribute widely: publish the workspace as an App, which packages selected reports/dashboards into a single, versioned, easy-to-navigate experience for end users.
5. Configure Row-Level Security role assignments (if applicable) before sharing broadly, so each user only sees their permitted data.