Power BI Basics
1. What is Power BI? basic
Power BI is Microsoft's business intelligence tool for connecting to data, cleaning and modelling it, and building interactive reports and dashboards that can be shared online.
2. What are the main components of Power BI? basic
Power BI Desktop (build reports), Power BI Service (publish, share and schedule refresh online), and Power BI Mobile (view reports on phones). Other parts include Report Server, Gateway and Dataflows.
3. What is the difference between Power BI Desktop and Power BI Service? basic
Desktop is a free Windows application for building data models and reports. The Service is the cloud platform where you publish, share, schedule refresh, build dashboards and manage access.
4. What is the difference between a report and a dashboard in Power BI? basic
A report has multiple pages with many visuals built from one data set. A dashboard is a single page of pinned tiles that can come from different reports, used for monitoring.
5. What is a data set in Power BI? basic
A dataset (now called a semantic model) is the data and model behind reports, including tables, relationships and measures. Many reports can use one shared semantic model.
6. What are the three main views in Power BI Desktop? basic
Report view for visuals, Data view to see tables, and Model view to manage relationships. Power Query Editor is opened separately for data transformation.
7. What data sources can Power BI connect to? basic
Excel, CSV, SQL Server and other databases, SharePoint, web pages, APIs, cloud services such as Azure, Google Analytics and Salesforce, and many more.
8. What are slicers? basic
Slicers are on-screen filters that let users click to filter the visuals on a page, for example by year or region.
9. What is the difference between a slicer and a filter? basic
A slicer is a visible control on the report canvas for users. Filters sit in the filter pane and can be set at visual, page or report level, mostly by the report builder.
10. What types of visuals are available? basic
Bar and column charts, line, area, pie, donut, tables, matrices, cards, KPI, gauge, maps, scatter, treemap, waterfall, funnel and custom visuals from the marketplace.
11. What is a card visual used for? basic
A card shows a single number, such as total sales. It is used for headline KPIs at the top of a report.
12. What is the difference between a table and a matrix visual? basic
A table shows flat rows and columns. A matrix allows row and column grouping with subtotals, like a PivotTable, and supports drill-down.
13. What is Power BI Pro and Premium? basic
Pro is a per-user licence to publish and share content. Premium gives dedicated capacity for large organisations, with higher limits, and can allow viewers without a Pro licence. Licence details change, so check Microsoft's current plans.
Power Query and Data Preparation
14. What is Power Query? basic
Power Query is the data connection and transformation tool in Power BI and Excel. You apply steps such as removing columns, filtering, merging and unpivoting, and they are recorded and repeated at every refresh.
15. What language does Power Query use? basic
M language. Most steps are created by clicking, and the M code is written automatically. You can edit it in the Advanced Editor for custom logic.
16. What is the difference between merge and append in Power Query? basic
Merge joins tables side by side using a matching column, like a SQL join. Append stacks tables on top of each other, like UNION.
17. What are the join types available in a Power Query merge? intermediate
Left outer, right outer, full outer, inner, left anti and right anti. Anti joins return rows with no match in the other table.
18. What does unpivot do? intermediate
Unpivot turns columns into rows. For example, month columns become one 'Month' column and one 'Value' column, which suits reporting and modelling much better.
19. What is query folding? intermediate
Query folding is when Power Query turns your steps into one native query, such as SQL, and the source database does the work. It makes refresh much faster. Some steps, like adding an index column, can break it.
20. How can you check if query folding is happening? advanced
Right-click a step and check if 'View Native Query' is enabled. If it is greyed out, folding has stopped at or before that step. Move foldable steps earlier.
21. What is the difference between Replace Values and Transform in Power Query? basic
Replace Values changes specific values in a column. Transform changes how the whole column is treated, such as to uppercase, trim, change data type or extract part of text.
22. How do you handle errors in Power Query? intermediate
Use Remove Errors or Replace Errors, fix the source cause, or use try ... otherwise in M. Check data types, because wrong type conversion is a common cause.
23. What is a parameter in Power Query? intermediate
A parameter is a stored value, such as a server name or file path, that can be reused in queries. It makes it easy to switch between environments or change the date range.
24. What is a function in Power Query and how do you create one? intermediate
You can turn a query into a reusable function that takes inputs. It is useful for applying the same steps to many files, such as combining all files in a folder.
25. How do you combine many files from a folder? intermediate
Use Get Data > Folder, then Combine & Transform. Power Query creates a sample transform and applies it to every file, so new files are included on refresh.
26. What is the difference between Enter Data and Get Data? basic
Get Data connects to an outside source. Enter Data lets you type a small table directly into the model, useful for mapping tables or lookup lists.
27. What does disabling load or enabling load mean for a query? intermediate
Disabling 'Enable load' keeps the query as a helper step for other queries but does not load it into the model. It keeps the model clean and smaller.
28. What is the difference between reference and duplicate in Power Query? intermediate
Duplicate copies the query steps as a separate query. Reference creates a new query that builds on the output of the original, so changes to the original flow through.
29. What does 'Use First Row as Headers' do? basic
It makes the first row of data the column names. It is often needed after loading Excel or CSV files where headers appear as the first row of data.
Data Modelling
30. Why is data modelling important in Power BI? basic
A good model makes reports fast, accurate and easy to build. Clean tables and correct relationships make measures give right answers across filters.
31. What is a star schema and why is it recommended? basic
A star schema has a central fact table with measures and surrounding dimension tables for descriptive attributes. It is simple, fast, and works best with Power BI's engine and DAX.
32. What types of relationships exist in Power BI? basic
One-to-many (most common), one-to-one, and many-to-many. Relationships can have single or both-direction filtering.
33. What is cardinality in a relationship? intermediate
Cardinality describes how rows in two tables relate: one-to-many, many-to-one, one-to-one or many-to-many. Getting it right matters for correct results.
34. What is cross-filter direction? intermediate
It controls how filters flow between related tables. Single direction filters from dimension to fact. Both directions can cause confusing results and slowdowns, so use it carefully.
35. What is an active vs inactive relationship? intermediate
Only one active relationship can exist between two tables. Others are inactive and can be activated inside a measure using USERELATIONSHIP. This is common with multiple date columns, such as order date and ship date.
36. What is a role-playing dimension? intermediate
It is a dimension used in several roles, such as a Date table used for order date and ship date. In Power BI you handle this with inactive relationships or duplicate date tables.
37. Why do you need a separate Date table? basic
A proper date table with continuous dates, marked as the date table, enables time-intelligence functions to work correctly and gives consistent year, quarter and month fields.
38. How do you create a Date table? intermediate
Use CALENDAR or CALENDARAUTO in DAX, or create it in Power Query. Add year, month, quarter and week columns, then mark it as a date table.
39. How do you deal with a many-to-many relationship? intermediate
Prefer to avoid it by adding a bridge (junction) table or a unique dimension table in the middle. If you use native many-to-many, understand how filters will propagate.
40. What are the storage modes in Power BI? intermediate
Import (data copied into the model, fastest), DirectQuery (queries sent to the source live) and Dual (acts as either). Composite models combine modes.
41. What is the difference between Import and DirectQuery? intermediate
Import loads a copy of data into memory, so visuals are fast, but data is as fresh as the last refresh. DirectQuery leaves data in the source and queries it live, which gives fresh data but is slower and limits some features.
42. When would you use DirectQuery? advanced
When data is too large to import, or you need near real-time data, or data rules forbid copying it. Be aware of slower visuals and the load on the source system.
43. What is a composite model? advanced
A composite model mixes Import and DirectQuery tables in one model, for example imported small dimensions with a DirectQuery fact table. It balances speed and freshness.
44. What is a calculated column versus a measure? intermediate
A calculated column is computed row by row at refresh and stored in the model. A measure is calculated at query time based on the current filters and is not stored. Measures are lighter and respond to filters.
45. When should you use a calculated column rather than a measure? intermediate
Use a column when you need a value per row to slice, filter or group by, such as an age band. Use a measure for aggregations like total, average or ratio.
46. What is a bridge table? advanced
A bridge table sits between two tables with a many-to-many relationship, holding the unique keys of both so each relationship becomes one-to-many.
DAX Fundamentals
47. What is DAX? basic
DAX (Data Analysis Expressions) is the formula language of Power BI, Power Pivot and Analysis Services. It is used to create measures, calculated columns and tables.
48. What is the difference between row context and filter context? basic
Row context means the formula is evaluated for one row at a time, as in a calculated column. Filter context is the set of filters from slicers, rows, columns and CALCULATE that decide which rows a measure sees.
49. What does CALCULATE do? intermediate
CALCULATE(expression, filter1, filter2...) evaluates an expression after changing the filter context. It is the most important DAX function, used for conditional totals and time intelligence.
50. What is the difference between SUM and SUMX? intermediate
SUM adds a single column. SUMX is an iterator: it goes row by row over a table, evaluates an expression for each row, and then adds the results, such as SUMX(Sales, Sales[Qty] * Sales[Price]).
51. What are iterator functions? intermediate
Iterators such as SUMX, AVERAGEX, MAXX and COUNTX evaluate an expression for each row of a table and then aggregate. They are needed when the calculation depends on several columns in a row.
52. What is the difference between CALCULATE and FILTER? intermediate
FILTER returns a table of rows meeting a condition and is an iterator. CALCULATE changes the filter context for an expression. Simple conditions in CALCULATE are faster than wrapping the table in FILTER.
53. What does ALL do in DAX? intermediate
ALL removes filters from a table or column. It is used in the denominator of percent-of-total measures: DIVIDE([Sales], CALCULATE([Sales], ALL(Product))).
54. What is the difference between ALL, ALLEXCEPT and ALLSELECTED? intermediate
ALL removes all filters on the given table or columns. ALLEXCEPT removes all except the named columns. ALLSELECTED removes filters inside the visual but keeps the filters from slicers outside, which suits percent of visible total.
55. What does VALUES do? intermediate
VALUES returns the unique values of a column in the current filter context. DISTINCT is similar, but VALUES can include a blank row if there are unmatched relationship values.
56. What is the use of DIVIDE compared with the / operator? intermediate
DIVIDE(a, b) handles division by zero safely, returning blank (or an alternate result) instead of an error.
57. How do you write a percent-of-total measure? intermediate
% of Total = DIVIDE([Sales], CALCULATE([Sales], ALL(Product[Category]))). Use ALLSELECTED instead of ALL to respect slicers.
58. What is the difference between COUNT, COUNTA, COUNTROWS and DISTINCTCOUNT? intermediate
COUNT counts numeric values in a column. COUNTA counts non-blank values of any type. COUNTROWS counts rows of a table. DISTINCTCOUNT counts unique values.
59. What does RELATED do? basic
RELATED fetches a value from the one-side table of a relationship into the current row of the many-side table. It is used in calculated columns.
60. What does RELATEDTABLE do? intermediate
It returns the related rows from the many-side table for the current row, which you can then aggregate, such as counting a customer's orders.
61. What does SWITCH do? intermediate
SWITCH(expression, value1, result1, value2, result2, else) is a cleaner alternative to nested IFs. SWITCH(TRUE(), condition1, result1, ...) handles ranges.
62. What are variables in DAX and why use them? intermediate
Variables are declared with VAR and returned with RETURN. They make formulas easier to read, avoid repeating calculations and often improve performance.
63. What is the difference between DISTINCT and VALUES in DAX? intermediate
Both return unique values. VALUES also includes the blank row when a relationship has unmatched rows, while DISTINCT does not.
64. What is context transition? advanced
Context transition happens when CALCULATE (or a measure reference) turns the current row context into an equivalent filter context. It is why calling a measure inside an iterator works row by row.
65. What is the difference between KEEPFILTERS and normal CALCULATE filters? advanced
A normal CALCULATE filter replaces existing filters on that column. KEEPFILTERS adds to existing filters instead, so both conditions apply.
66. What does TREATAS do? advanced
TREATAS applies the values of one table as filters on columns of another, even without a relationship. It is used for virtual relationships.
67. What is the difference between SUMMARIZE and SUMMARIZECOLUMNS? advanced
Both create grouped tables. SUMMARIZECOLUMNS is generally faster and more flexible for queries, while SUMMARIZE is older and best used just for grouping. Know that behaviour differs in measures.
68. What does ISBLANK do and when is it used? intermediate
ISBLANK checks if a value is blank. It is used to show a default, hide empty results or avoid errors in measures.
69. What does SELECTEDVALUE do? intermediate
SELECTEDVALUE(column, alternate) returns the value if only one value is selected in the current context, otherwise the alternate result. It is common for dynamic titles and what-if parameters.
70. How do you rank items in DAX? intermediate
Use RANKX(ALL(Product[Name]), [Sales]) to rank products by sales. Use ALL or ALLSELECTED to define the group being ranked.
71. How do you write a Top N measure? advanced
Use TOPN(n, table, expression) inside CALCULATE or as a filter, or use RANKX and keep rank less than or equal to N. The Top N filter in the filter pane is a simpler visual-only option.
DAX Time Intelligence
72. What is time intelligence in DAX? intermediate
It is a group of functions that compare periods, such as year-to-date or same period last year. They need a proper Date table marked as the date table.
73. How do you calculate year-to-date sales? intermediate
Sales YTD = TOTALYTD([Total Sales], 'Date'[Date]) or CALCULATE([Total Sales], DATESYTD('Date'[Date])).
74. How do you calculate sales for the same period last year? intermediate
Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date])). Then YoY growth is DIVIDE([Total Sales] - [Sales LY], [Sales LY]).
75. What is the difference between DATEADD and SAMEPERIODLASTYEAR? intermediate
SAMEPERIODLASTYEAR shifts dates back one year only. DATEADD can shift by any number of days, months, quarters or years, forward or back.
76. How do you calculate a rolling 12-month total? intermediate
CALCULATE([Total Sales], DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)).
77. What does PARALLELPERIOD do? intermediate
It returns a full period (month, quarter or year) shifted from the current dates. It returns whole periods, while DATEADD keeps the same dates shifted.
78. Why do time intelligence functions return wrong results? advanced
Common causes are a missing or non-continuous date table, the table not marked as a date table, using the date column from the fact table instead of the Date table, or an inactive relationship.
79. How do you calculate month-over-month growth? intermediate
Create a previous month measure using DATEADD('Date'[Date], -1, MONTH) or PREVIOUSMONTH, then DIVIDE([Sales] - [Sales PM], [Sales PM]).
80. How do you handle fiscal years in Power BI? advanced
Add fiscal year columns to the Date table, or use the year-end date argument in functions, such as TOTALYTD([Sales], 'Date'[Date], "31/3") for a March year end.
81. What is the difference between DATESYTD and TOTALYTD? intermediate
They give the same result. DATESYTD returns the dates and is used inside CALCULATE, while TOTALYTD is a shortcut that evaluates the expression directly.
Security, Sharing and Service
82. What is Row-Level Security (RLS)? intermediate
RLS restricts which rows a user can see in a data model. You define roles with DAX filters, such as [Region] = USERPRINCIPALNAME()-based rules, and assign users to roles in the Service.
83. What is the difference between static and dynamic RLS? advanced
Static RLS hard-codes a filter per role, such as 'West region'. Dynamic RLS uses the signed-in user's identity (USERPRINCIPALNAME()) and a mapping table, so one role works for everyone.
84. How do you test RLS? intermediate
In Desktop use 'View as roles'. In the Service, open the semantic model's security settings and use 'Test as role'. Always test with a user who is in only one role.
85. What is a workspace in Power BI Service? intermediate
A workspace is a shared area where teams collaborate on reports, dashboards and semantic models, with roles like Admin, Member, Contributor and Viewer.
86. What is a Power BI app? intermediate
An app is a packaged collection of dashboards and reports published from a workspace to a wider audience, giving a clean, controlled experience for business users.
87. How do you share a report in Power BI? basic
Publish it to a workspace, then share by link, add people to the workspace, or publish an app. Viewers usually need the right licence and permissions.
88. What is a gateway and when do you need it? intermediate
A data gateway is software that securely connects the Power BI Service to on-premises data sources for scheduled refresh and live queries. You need it when data is not in the cloud.
89. What is the difference between a personal and an on-premises (standard) gateway? intermediate
A personal gateway is for one user and Import refresh only. The standard gateway is for a team or organisation, supports both Import and DirectQuery, and is managed centrally.
90. How do you schedule a data refresh? basic
Publish the dataset, open its settings in the Service, enter credentials, and set the schedule. Import mode needs a refresh to show new data.
91. What is incremental refresh? intermediate
Incremental refresh refreshes only new or changed data, such as the last few days, instead of the full table. It needs date parameters RangeStart and RangeEnd and speeds up large refreshes.
92. What is a dataflow? intermediate
A dataflow is a reusable Power Query transformation that runs in the cloud and stores results for use by several semantic models. It avoids repeating the same cleaning in every report.
93. What is the difference between a dataflow and a dataset? intermediate
A dataflow handles data preparation and storage of cleaned tables. A dataset (semantic model) holds the model, relationships and measures that reports use.
94. What are bookmarks in Power BI? intermediate
Bookmarks save the state of a page, including filters, slicers and visibility. They are used for buttons that switch views or for guided storytelling.
95. What is drillthrough? intermediate
Drillthrough lets users right-click a data point and jump to a detail page filtered to that item, such as a customer detail page.
96. What is the difference between drill-down and drill-through? basic
Drill-down moves through a hierarchy within the same visual, such as year to month. Drill-through navigates to another page with details for the selected item.
97. What are tooltips and report page tooltips? intermediate
Tooltips show extra information when you hover over a data point. A report page tooltip is a small custom page shown as the tooltip, with its own visuals.
98. How do you refresh a report that uses a file on SharePoint or OneDrive? intermediate
Connect to the file through its web URL, so the Service can refresh it without a gateway. Make sure credentials are set in the dataset settings.
99. What is a deployment pipeline? intermediate
A deployment pipeline moves content through development, test and production stages in the Service, so changes can be checked before users see them.
100. What is the XMLA endpoint? advanced
The XMLA endpoint lets external tools, such as Tabular Editor or SSMS, read and write the semantic model in Premium or Fabric. It is used for advanced management and automation.
Scenario Questions
111. How would you build a sales dashboard from scratch? intermediate
Understand the questions and KPIs, connect and clean the data in Power Query, build a star schema with a Date table, write measures such as total sales and growth, design the pages with the key numbers first, test the numbers against source data, and publish with refresh and access set up.
112. Your report numbers do not match the source. What do you check? advanced
Compare filters and date ranges, check relationships and cardinality for duplicated rows, check that totals use the right measure, look for blanks and unmatched keys, and test with a simple table visual against the source.
113. A report works in Desktop but refresh fails in the Service. Why? advanced
Common causes are missing or expired credentials, a gateway that is offline or lacks the data source, privacy level problems, file paths that only exist on your computer, or the source being unreachable.
114. How do you show only the current user's data? intermediate
Use dynamic RLS with a mapping table of users to regions or accounts and a DAX filter based on USERPRINCIPALNAME(). Test the roles before sharing.
115. How do you create a dynamic chart title? intermediate
Create a measure that builds text from the selected filters with SELECTEDVALUE, then use conditional formatting (fx) on the visual's title to use that measure.
116. How would you let users switch between measures in one visual? advanced
Create a disconnected table listing the measure names, use it in a slicer, and write a measure with SWITCH(SELECTEDVALUE(...), ...) that returns the matching measure. This is a field parameter-style approach.
117. What is a field parameter? intermediate
A field parameter lets report users choose which fields or measures a visual shows using a slicer, with no complex DAX, so one visual can serve many views.
118. What is conditional formatting in Power BI? intermediate
It changes colours, icons or data bars based on rules or values, such as red for sales below target. It helps people spot problems quickly.
119. How do you handle a many-to-many relationship in a sales and budget scenario? advanced
Create shared dimension tables, such as Date and Product, and relate both the sales and budget fact tables to them. Both facts then filter by the same dimensions without a direct many-to-many link.
120. What is a what-if parameter? intermediate
A what-if parameter creates a slider with a range of values, and a measure to read the selection. Users can test scenarios such as a discount percentage and see the impact on profit.
