Power BI Interview Questions

"In a normal project I start in Power BI Desktop. That's the free Windows app where I connect to the sources, clean the data in Power Query, build the model and relationships, write DAX measures and design the report pages. When it's ready I publish to the Power BI Service, the cloud side. There I put it in a workspace, share it with users, usually through an app, and set a scheduled refresh. If the data lives on a server inside the company network, the Service can't reach it directly, so we install an on-premises data gateway to handle the refresh. Users then view the reports in the browser, in Teams or on the mobile app. Companies that can't use the cloud can run Power BI Report Server instead, and for invoice-style printable reports there are paginated reports."

"A report is what I build in Desktop. It can have many pages, it sits on top of one semantic model, and it's fully interactive: slicers, cross-filtering, drill-down, tooltips. A dashboard only exists in the Power BI Service. It's a single page of tiles, and I make it by pinning visuals from reports, which can come from different reports and different semantic models. That's its strength: a manager can see sales, stock and support numbers on one screen. But a dashboard has no slicers of its own, and clicking a tile opens the report it came from. You can also set data alerts on card, KPI or gauge tiles, so someone is notified when a number crosses a line. So reports are for analysis and dashboards are for monitoring. In practice many teams now share an app with a strong summary page instead."

"Power Query is the data preparation layer in Power BI. I use it to connect to sources like a SQL database, Excel files or SharePoint, then fix the data before it reaches the model: remove columns I don't need, set data types, split columns, filter out bad rows, replace values and combine tables. Every click becomes an applied step, and behind the scenes each step is written in a language called M. The key point is that it's a recipe, not a one-off edit. When the report refreshes tomorrow, Power Query replays every step, in the same order, on the new data. So if a step depends on something fragile, like a column name the source team later renames, the refresh fails. That's why I name my steps clearly, remove unneeded columns early and keep hard-coded values to a minimum."

"Merge is a join and append is a stack. If I have an orders table and a customers table and I want the customer's region next to each order, I merge on customer ID, pick a join kind, usually left outer so I keep every order, then expand the region column. Anti joins are handy too, for example a left anti join to find orders whose customer doesn't exist. Append is for when the same shape of data is split across tables, like one sales file per region or per year, and I want one table with all the rows. Append matches columns by name, so if one file says 'Qty' and another says 'Quantity', I get two half-empty columns. In a merge the trap is duplicate keys on the right-hand table, which quietly multiplies my rows, so I always check row counts afterwards."

"Query folding means Power Query translates my applied steps into the source's own language. Against a SQL database, my filter, column removal and group-by become one SELECT statement with a WHERE and a GROUP BY. The database does the work and only the result comes over the network. If folding breaks, Power Query pulls the rows into its own engine and does the work there, which is much slower on big tables. To check, I right-click a step: if View Native Query is available, that step folds. A greyed-out option is only a hint, since some connectors never show it, and in Power Query Online there are folding indicators next to each step. Some steps can't fold, like adding an index column or merging a SQL table with an Excel file, and once one step breaks folding, the later steps usually don't fold either. So I filter and remove columns first. It also matters for incremental refresh, which relies on the date filter folding back to the source."

"A sheet with Jan, Feb and Mar as columns is easy for people to read but bad for a model. I can't connect it to a date table, I can't write one measure across all months, and next month a new column appears that my visuals don't know about. So in Power Query I select the columns that describe the row, like product and region, and choose Unpivot Other Columns. That turns every month column into rows with two columns: an attribute holding the month name and a value holding the amount. I rename them to Month and Amount, set the types, and convert the month into a proper date so it joins to my date table. I pick Unpivot Other Columns rather than unpivoting only the selected month columns because when finance adds a new month, it gets picked up automatically on the next refresh."

"In a star schema I keep a fact table in the middle, like sales, with one row per transaction, the numbers I'll add up, and keys to the dimensions. Around it sit dimension tables like date, product, customer and store, holding the descriptive columns people slice by. Each dimension links to the fact one-to-many, filtering in one direction. Power BI's engine is built for this. Descriptive text lives once in a small table instead of being repeated on millions of rows, so the model compresses better. Filters travel one clear way, from dimension to fact, so results are predictable and the DAX stays simple. The big win comes with a second fact, like budgets or returns: both share the same date and product dimensions, and one slicer filters both. That's awkward with one flat table, and a snowflake of chained lookup tables just makes the filter paths longer for no real gain."

"Cardinality says how many rows on each side can match. One-to-many is the normal case: one product row, many sales rows, and the product key must be unique on the one side. One-to-one is rare and usually means the two tables should just be merged. Many-to-many happens when neither column is unique. A common example is targets set per category joined to a product table, where each category appears many times. Another is customers and bank accounts, where one customer holds several accounts and an account can have joint holders. For targets I'd build a small category dimension with unique values and relate both tables to it. For joint accounts I'd use a bridge table between customer and account. What I avoid is choosing many-to-many just because Power BI refused a one-to-many over a duplicate key I didn't expect. That's usually dirty data, and it needs fixing."

"Cross-filter direction decides which way a filter travels along a relationship. With single, the default for one-to-many, a filter on product flows down to sales, which is what we want. With both, it also flows back up, so filtering sales can filter product. That's sometimes useful, for example so a product slicer only lists products that actually sold. The risk is that once a few relationships go both ways, there can be more than one path between two tables, and Power BI either won't let a relationship be active or gives numbers that are hard to explain. It also makes queries heavier. So I keep everything single by default. When one measure needs the reverse flow, I use CROSSFILTER inside CALCULATE for just that measure. For the slicer case, a visual-level filter on the slicer where sales is not blank does the job without touching the model."

"Power BI allows only one active relationship between two tables, so I'd make order date to the date table the active one, since most measures use it, and create ship date to the date table as an inactive relationship, which shows as a dashed line. For measures that need ship date, I write CALCULATE with USERELATIONSHIP, which switches on the inactive relationship just for that calculation. That keeps one date table and one set of slicers. The other option is a role-playing copy: a second date table just for ship dates, with its own active relationship. I'd choose that when users want to filter by order month and ship month at the same time on one page, or when lots of measures would all need USERELATIONSHIP. The cost is one more table and slicers that users have to tell apart."

"A calculated column is evaluated once per row when the data refreshes, and the result is stored in the table like any other column. So it uses memory, and it only knows about its own row. A measure isn't stored at all. It's calculated the moment a visual asks for it, using whatever filters are active: the slicer, the row in the matrix, the bar in the chart. My rule is simple. If I need the result on an axis, in a slicer, or to group rows, like an age band or a price tier, it's a column, and ideally I create it in Power Query or the source rather than in DAX. If it's a number people add up, average or compare, like sales or margin, it's a measure. A common mistake is a margin column worked out row by row and then summed, which gives a wrong total. A measure dividing total profit by total sales gets it right."

"Row context means DAX knows which row it's on. You get it in a calculated column and inside iterators like SUMX or FILTER, so I can multiply quantity by price and it uses that row's values. But row context doesn't filter anything. Filter context is the set of filters in play when a measure runs: slicers, the row and column of a matrix, filters added by CALCULATE. It decides which rows are visible. Context transition is the bridge. When CALCULATE runs inside a row context, it turns the current row into an equivalent filter. The classic demo is a calculated column on the customer table. SUM of sales amount gives the grand total on every row, because nothing filters sales. Wrap it in CALCULATE and each customer gets their own sales. And because every measure is implicitly wrapped in CALCULATE, calling a measure inside SUMX triggers context transition too, which is powerful but can be slow on big tables."

"CALCULATE takes an expression, usually a measure, and evaluates it under a filter context that I change with its filter arguments. If I write CALCULATE of total sales where product colour is red, that filter replaces any filter already on colour. So if the slicer says blue, the measure still shows red sales, which surprises people. Filters on other columns, like year, stay in place. If I want to respect the slicer and only narrow it, I wrap the condition in KEEPFILTERS, and then with blue selected the result is blank, because nothing is both blue and red. To go the other way and drop a filter, I use REMOVEFILTERS or ALL on that column or table. Replace, keep and remove cover most of what people do with CALCULATE, and knowing which one you're doing is the difference between a measure that's right and one that's right by accident."

"SUM takes one column and adds it up for whatever rows the filter context lets through. SUMX is an iterator. I give it a table and an expression, it goes row by row, works out the expression for each row, then sums the results. The classic case is revenue when the table has quantity and unit price but no amount column. SUM of quantity times SUM of price would be wrong, because it multiplies two totals. SUMX over sales of quantity times price does the multiplication per row and then adds, which is correct. Under the hood SUM of a column is shorthand for SUMX over that column, so plain SUM isn't magically faster. What I watch with SUMX is iterating a huge table while calling a measure in the expression, because that triggers context transition on every row. The same pattern exists as AVERAGEX, MAXX, COUNTX and others."

"I'd write it with two variables. The current value is just total sales. The denominator is total sales inside CALCULATE with REMOVEFILTERS on the category column, which drops the category filter coming from the matrix row but leaves region, date and everything else alone. So when the user picks a region, each category shows its share of that region's sales, which is what they expect. Then I return DIVIDE of the two, which handles a zero or blank denominator safely, and format the measure as a percentage. If I'd removed filters from the whole sales table instead, the region filter would disappear too, and the shares would no longer add up to a hundred percent within a region. And if the business wants the share among only the categories ticked in a slicer, I'd swap to ALLSELECTED on the category column."

"Time intelligence functions work by moving date ranges around: every day from the start of the year to now, or the same days one year back. That only works with a date table that has one row for every day, no gaps, whole years, and a unique date column. I build it in Power Query or with CALENDAR in DAX, add year, quarter and month columns, mark it as the date table and relate it to the fact tables. I also turn off auto date/time, so Power BI stops making hidden date tables for every date column. Then year to date is CALCULATE of total sales over DATESYTD on the date column, and last year is CALCULATE over SAMEPERIODLASTYEAR. Growth is the difference divided by last year, using DIVIDE so a new product with no history shows blank rather than an error. For a fiscal year, DATESYTD accepts a year-end date as a second argument."

"The total row doesn't add up the numbers above it. It evaluates the measure again in the total's own filter context, where the row filter is gone. For a simple sum that gives the same answer, but for anything non-additive it doesn't. Say I have a bonus measure: if a salesperson's sales pass a target, pay a fixed bonus. Per row it works. At the total, the measure checks whether everyone's sales combined pass the target, and pays one bonus. The fix is to make the total iterate: SUMX over VALUES of the salesperson key, calling the bonus measure. Each salesperson is evaluated on their own through context transition, and the results are added. Before fixing anything, though, I ask what the total should mean, because for a distinct count of customers, a total below the sum of the rows is correct. A customer who bought in two regions is still one customer."

"My default is import. The data is loaded into the in-memory engine, compressed, and visuals come back fast, with all of DAX and Power Query available. The cost is freshness: users see data as of the last refresh, and scheduled refreshes are limited per day depending on the licence, as is model size. DirectQuery leaves the data at the source. Every interaction sends queries to the database, so numbers are close to real time and huge tables don't have to fit in memory. But each visual is only as fast as the source, a busy page can fire many queries at the database, and some Power Query and DAX features are restricted. I'd pick DirectQuery when users truly need very fresh data or policy says the data can't be copied. Often the best answer is a composite model: import the dimensions and a summary table, and keep the large detail table in DirectQuery."

"The Service lives in the cloud, so it can't reach a database server inside our network on its own. The bridge is the on-premises data gateway. It's installed on an always-on machine inside the network, not someone's laptop, and it only makes outbound connections to the Service, so no inbound firewall ports need opening. I register it, then on the gateway I add the data source with the same server and database names I used in Desktop, plus a service account's credentials. In the semantic model settings I map the model to that gateway connection, set the refresh schedule and time zone, and turn on failure emails. There's also a personal mode gateway, but it's tied to one user, so for anything shared I use standard mode, ideally clustered on two machines so one reboot doesn't stop refresh. Purely cloud sources usually don't need a gateway at all."

"I'd use dynamic RLS. First a security table with two columns: the user's sign-in email and a region they may see. A manager covering two regions just gets two rows. Then I create one role in Desktop with a DAX filter on the region dimension: keep a region only if it appears in the security table for the email that USERPRINCIPALNAME returns, which is the signed-in user. Because the region table filters the sales facts, each manager sees only their numbers. After publishing, I add the managers, ideally as a security group, to that role on the semantic model. A new manager then means a new row, not a new role. I test with View as, choosing the role and typing another user's email, in Desktop and in the Service. One thing people miss: RLS only restricts people with viewer access. Workspace admins, members and contributors see everything, so managers get an app or viewer access, never edit rights."

"I'd say yes to the goal and no to sharing it today as it is. Once salary data reaches a large list it can't be taken back, and untested RLS is exactly how that happens. So I'd explain that in one sentence and offer something for today: a version of the report on a model without the salary columns, or with only totals, which is usually what a wide audience needs anyway. Then I'd do the proper job. I'd test the roles with View as for a few real users from different teams, check that nobody on the list has edit rights in the workspace, since RLS doesn't restrict them, and share through an app with viewer access. If individual salaries shouldn't be visible to that audience at all, I'd take them out of the shared model or use object-level security. And I'd give the manager a specific time for the full version, so it doesn't feel like a brush-off."

"I don't guess, I measure. I open the report in Desktop, start Performance Analyzer and refresh the visuals. It shows each visual's time split into the DAX query, the visual display, and other, which is mostly waiting for other visuals to finish. If one visual's DAX query is slow, I copy it into DAX Studio, turn on server timings and see whether the time is in the storage engine, which scans the data, or the formula engine, which usually points at an iterator or a complex measure. If lots of visuals are each a bit slow, the problem is often the page itself: twenty visuals querying at once, a table visual with thousands of rows, or slicers on columns with huge numbers of values. Then I check the model with VertiPaq Analyzer for oversized columns and bi-directional relationships. And I confirm the storage mode, because in DirectQuery the slow part may be the source database."

"Size in the import engine is driven mostly by columns and how many distinct values each holds, so that's where I start. I run VertiPaq Analyzer to list the biggest columns. The top offenders are usually things nobody reports on: transaction GUIDs, free-text comments, a datetime with seconds. I remove what isn't used. For the rest I cut cardinality: split datetime into a date column and a time column, or drop the time if nobody needs it, round amounts to the precision people use, and make relationship keys integers. I turn off auto date/time, which builds a hidden date table for every date column. Calculated columns move to Power Query or the source. Then I question grain and history. If people only look at daily totals per store, a daily summary can replace millions of receipt rows, and if nobody looks past three years, I don't load ten. Finally, incremental refresh so each refresh only reloads recent data."

"I inherited a finance model that took about an hour to refresh, and some pages took well over ten seconds to load. It was one giant flat table joined in Power Query from five sources, plus dozens of calculated columns and several bi-directional relationships added to make slicers behave. I ran VertiPaq Analyzer and Performance Analyzer first. Most of the size was two columns: a transaction ID nobody reported on and a datetime with seconds. The slow visuals were measures using FILTER over the whole table. I rebuilt it as a star schema, moved the calculated columns into the SQL views, dropped the unused columns, made every relationship single direction, and rewrote the slowest measures to filter columns instead of tables. Refresh fell to a few minutes and the worst page loaded in about two seconds. I also wrote a short model guide, because the real problem was five people each adding their own fix."

"I start with the question the reader is trying to answer, not the visual. If they want to know how something changed over time, it's a line chart with dates along the bottom. If they're comparing categories, like sales by region, it's a bar chart, sorted so the biggest is on top. For one number that matters, like this month's sales against target, a card or KPI visual. A scatter chart for relationships, like discount against margin by product. Tables and matrices when people need exact figures or want to look up a specific item. Pie and donut charts I only use for two or three parts of a whole, because people can't compare angles well once there are more slices. I avoid 3D and heavy colour, and I use colour to highlight, like the one region below target in red, rather than giving every bar its own colour."

"I'd start by sitting in on the meeting, or asking what gets decided there. Usually it's: are we on target this week, which regions or products are behind, and why. That gives me a short list of measures, like sales, target, gap to target, last year and open pipeline, and I'd get each one defined in writing, including what counts as a sale and which date we use. Then I check the sources, build a star schema with a proper date table, and reconcile my totals with finance before any design work. The first page answers the headline question in five seconds: a few cards against target, a trend line with last year, and a bar chart of regions sorted by gap. Drill-through pages give detail per region or product. If regional managers use it too, I add RLS. I publish through an app, schedule refresh before the meeting, and after a few meetings I ask what they actually looked at and cut the rest."

"In my last role I built an inventory report for store managers that I was proud of: six pages and lots of slicers. A month later the usage report showed only a handful of views, and managers were still emailing the stock team for spreadsheets. So I sat with two managers and watched them try it. They wanted one thing every morning: what's about to run out in my store. That answer was on page four, behind two slicers. I rebuilt the first page around it: their store applied automatically through RLS, a short table of items below reorder level sorted by days of stock left, and an export button. Everything else moved to detail pages. Within a few weeks most managers opened it before the morning stock call, and the spreadsheet requests stopped. Since then I build the first page around one question and watch someone use it before I call it done."

"I'd start by asking what they want to see at a glance and what annoys them about clicking. Often the real complaint is that they had to hunt through tabs to find something. Then I'd explain the trade-off plainly: twenty-five visuals on one page means small, hard-to-read charts, and every visual sends its own query, so the page loads slower, which they'd notice too. What I'd offer is a summary page with the handful of numbers they check every time, each against target or last year, with tooltips that show detail on hover, so most answers need no click at all. For the rest, drill-through from any region or product to a detail page. I'd build a quick version, let them use it in their next review, and adjust. If they still want more on the page after that, I'd add what they actually missed, not all twenty-five."

"At my last company, the sales director's report showed monthly revenue a little higher than finance's figure, and finance's number was the one the board saw. I didn't argue about which was right. I exported both totals by day for the month and put them side by side, and the whole gap sat on a handful of days. Drilling into those days, it was orders that had been cancelled and re-entered. Finance excluded cancelled orders, and my measure didn't filter on status. So the difference was a definition, not a bug. I agreed with the finance lead that revenue means invoiced and not cancelled, changed the measure, wrote that definition into the measure's description, and added a small reconciliation page showing our total next to the ledger total for each month. After that, when someone questioned a figure, the answer was already on the page."

"I wouldn't pick one quietly, and I wouldn't let one word mean two things on the same page. First I'd write both definitions down exactly. Usually sales means booked orders on the order date, and finance means invoiced revenue, net of returns, on the invoice date. Both are legitimate; they answer different questions. So I'd build both measures with names that say what they are, like booked sales and recognised revenue, and put the definition in each measure's description. Then I'd take it to whoever sponsors the dashboard, with one concrete example of why the numbers differ, and ask which one is the headline figure. The other can sit beside it or on a detail page. Once it's agreed, I'd keep those measures in a shared semantic model, so the next report reuses them instead of restarting the argument."