Power BI Rapid-Fire Questions &amp; Answers

Show answer

A report is a multi-page, fully interactive document built on one semantic model, with slicers, filters, drill-through and cross-highlighting. A dashboard is a single-page canvas in the Power BI Service made of pinned tiles that can come from several reports and models; it is used for a quick at-a-glance view. Dashboards are created only in the Service, while reports are usually built in Desktop.

What does Unpivot do and when would you use it?

Show answer

Unpivot turns columns into rows, creating attribute and value columns. It is used when data comes in a wide, report-style layout, such as months as separate columns Jan, Feb, Mar. Unpivoting them into a Month column and an Amount column gives a tall, tidy table that works well with relationships, a date table and DAX. Unpivot Other Columns is safer because new months are handled automatically.

What is conditional formatting and where can you apply it?

Show answer

Conditional formatting changes colours, icons, data bars or font based on values or rules, helping users spot good and bad performance quickly. You can apply it to table and matrix cells, backgrounds, font colour, chart data colours and titles using field values, rules or a measure that returns a colour code. A measure returning hex colours gives full control, such as red when below target.

What is a dataflow and how is it different from Power Query in Desktop?

Show answer

A dataflow runs Power Query transformations in the Power BI Service and stores the result in the cloud, so multiple semantic models can reuse the same cleaned tables. Desktop Power Query logic lives inside one model only. Dataflows Gen2 in Fabric can also write output to lakehouses or warehouses. Dataflows centralise preparation logic, reduce duplicate source load and separate data prep from modelling.

What is DAX?

Show answer

DAX, Data Analysis Expressions, is the formula language for Power BI, Analysis Services and Power Pivot. It is used to create measures, calculated columns, calculated tables and security rules. DAX looks like Excel formulas but works on tables and columns with filter context. Key functions include SUM, CALCULATE, FILTER, ALL, RELATED, time intelligence functions and iterators like SUMX.

Explain row context and filter context.

Show answer

Row context means DAX is evaluating one row at a time, which happens in calculated columns and iterators like SUMX, so you can refer to column values of the current row. Filter context is the set of filters applied to the model from slicers, visual rows, columns and CALCULATE. Measures are evaluated in filter context. Row context does not filter the model unless converted by context transition.

What is a semantic model in Power BI?

Show answer

A semantic model, earlier called a dataset, is the published data model behind reports: tables, relationships, measures, hierarchies, security roles and refresh settings. Many reports can connect to one shared semantic model, which keeps business logic and numbers consistent across teams. Certified or promoted endorsements help users find trusted models. Build permission controls who can create new reports on it.

What is query folding and why does it matter?

Show answer

Query folding is when Power Query translates transformation steps into a native source query, such as SQL, so the source does the work and only the result is loaded. It makes refreshes much faster and is required for incremental refresh and efficient DirectQuery. Steps like filtering, removing columns and grouping usually fold on SQL sources, while some custom M functions or adding an index column can break folding.

How do you choose the right chart type in Power BI?

Power BI Service, Sharing and SecurityEasy

Show answer

Match the chart to the question. Use bar or column charts to compare categories, line charts for trends over time, stacked bars for part-to-whole across few categories, scatter charts for relationships, maps only for true geographic patterns, cards and KPI visuals for single numbers, and tables or matrices when users need exact values. Avoid pie charts with more than four or five slices.

What are bookmarks and how are they used?

Visualisation and Report DesignMedium

Show answer

Bookmarks capture the current state of a report page, including filters, slicers, visual visibility and sort order. They are used for storytelling, toggling between chart and table views with buttons, creating custom navigation, and resetting filters. Combined with the selection pane, they show and hide visuals. Too many bookmarks become hard to maintain, so field parameters are often a simpler alternative for switching measures.

What is row-level security in Power BI?

Show answer

Row-level security restricts which rows of data a user can see in a semantic model. You define roles in Desktop with DAX filter expressions on tables, such as Store[Region] = "North", then assign users or security groups to roles in the Service. Filters propagate through relationships to fact tables. RLS applies to viewers; workspace Admins, Members and Contributors can see all data.

What are active and inactive relationships?

Data ModellingMedium

Show answer

Only one active relationship can exist between two tables along a filter path; additional relationships become inactive and appear as dashed lines. For example, Sales may have OrderDate and ShipDate both linked to Date, with OrderDate active. An inactive relationship is used only inside a measure with USERELATIONSHIP, such as CALCULATE([Sales], USERELATIONSHIP(Sales[ShipDate], 'Date'[Date])).

What is the difference between SUM and SUMX?

Show answer

SUM aggregates a single column, such as SUM(Sales[Amount]). SUMX is an iterator: it evaluates an expression for each row of a table and then sums the results, such as SUMX(Sales, Sales[Qty] * Sales[Price]). Use SUMX when you need row-level calculations before aggregating. Other iterators include AVERAGEX, MAXX, COUNTX and RANKX.

What are Import, DirectQuery and Live Connection modes?

Power BI FundamentalsMedium

Show answer

Import loads a compressed copy of data into the in-memory VertiPaq engine, giving the fastest queries but requiring refresh. DirectQuery sends queries to the source at view time, so data is current but performance depends on the source and some DAX is limited. Live Connection connects a report to an existing semantic model or Analysis Services model without its own model. Composite models mix Import and DirectQuery tables.

What makes a good dashboard design?

Show answer

A good dashboard answers specific questions for a specific audience. Put the most important KPIs at the top left, follow a logical flow from summary to detail, use a few consistent colours with one highlight colour, and choose simple visuals like bar and line charts. Remove clutter such as 3D effects and heavy borders, label clearly, show context like targets or last year, and keep each page to a manageable number of visuals.

What are query parameters in Power Query?

Show answer

Parameters are named values used inside queries, such as server name, database, file path or a date range. They let you switch from a development to a production database without editing each query, and they are required for incremental refresh through RangeStart and RangeEnd. Parameters can be changed in the Service under semantic model settings, which helps deployment pipelines between environments.

What is the difference between Power BI and Excel for reporting?

Power BI Service, Sharing and SecurityEasy

Show answer

Excel is flexible for ad hoc analysis and small models but struggles with large data, version control and secure sharing. Power BI handles millions of rows with a compressed columnar engine, central semantic models, scheduled refresh, row-level security and interactive web and mobile sharing. They work together: Power Query and Power Pivot exist in both, and users can analyse Power BI models in Excel.

What is the grain of a fact table and why does it matter?

Data ModellingMedium

Show answer

Grain is the level of detail of one fact row, such as one row per order line per day. It must be defined clearly before building measures, because mixing grains in one table, for example daily sales and monthly targets, causes double counting and wrong totals. Facts at different grains should be separate tables related to dimensions at the matching level.

What are subscriptions and data alerts in the Power BI Service?

Show answer

Subscriptions email a snapshot of a report page or dashboard to users on a schedule, or after data refresh, with a link to the live content. Data alerts notify users when a value on a dashboard card, KPI or gauge tile crosses a threshold, such as daily sales below a target. Paginated reports support subscriptions with attachments like PDF or Excel. Power Automate can extend alerts into workflows.

What does CALCULATE do?

Power BI Service, Sharing and SecurityEasy

Show answer

CALCULATE evaluates an expression in a modified filter context. Its filter arguments can add, replace or remove filters, for example CALCULATE([Total Sales], 'Product'[Category] = "Electronics") replaces any category filter with Electronics. It also performs context transition when used inside a row context, turning the current row into an equivalent filter. It is the most important function in DAX.

What is an on-premises data gateway and when is it needed?

Show answer

The on-premises data gateway is software installed on a machine inside the company network that securely connects the Power BI Service to on-premises sources such as SQL Server, file shares or Oracle. It is needed for scheduled refresh and DirectQuery to those sources. Standard mode supports many users and is centrally managed; personal mode is for a single user. Cloud sources usually do not need a gateway.

What is the difference between sharing a report, publishing an app and using a workspace?

Power BI Service, Sharing and SecurityMedium

Show answer

A workspace is where creators collaborate. Direct sharing gives one person or group access to a single report, which is quick but hard to govern at scale. An app packages reports and dashboards from a workspace for consumers, with audiences controlling who sees which content, and updates only when republished. Apps are the recommended way to distribute content to large business audiences.

What licence types exist for Power BI?

Power BI FundamentalsMedium

Show answer

Power BI Free lets a user build in Desktop and use My workspace but not share content. Power BI Pro is needed to publish to shared workspaces and share with other Pro users. Premium Per User adds larger models, more refreshes and premium features per user. Fabric capacity (F SKUs) provides dedicated capacity; at F64 and above, viewers with a free licence can consume shared content.

What is Power Query and where is it used?

Performance Optimisation and AdministrationEasy

Show answer

Power Query is the data connection and transformation engine in Power BI, Excel, dataflows and Fabric Data Factory. It lets you connect to hundreds of sources and shape data with a recorded series of steps, such as removing columns, changing types, merging and unpivoting. Behind the interface each step is written in the M language. Steps re-run automatically on every refresh, making cleaning repeatable.

What modelling practices reduce Power BI model size?

Show answer

Remove unused columns and rows, avoid high-cardinality columns like GUIDs and datetime with seconds by splitting date and time or rounding, use integer surrogate keys, prefer measures over calculated columns, turn off Auto date/time, and choose the right data types, such as fixed decimal. Load only the history users need, and summarise data where detail is not required.

What is the difference between static and dynamic RLS?

Power BI Service, Sharing and SecurityMedium

Show answer

Static RLS hard-codes values in each role, such as one role per region, which becomes hard to manage as regions grow. Dynamic RLS uses one role with a filter based on the logged-in user, such as Users[Email] = USERPRINCIPALNAME(), with a security mapping table linking users to the regions or managers they may see. Dynamic RLS scales better and is driven by data rather than role edits.

What are report themes and why use them?

Show answer

A report theme is a JSON file that sets default colours, fonts, visual styles and backgrounds for all visuals. Using a company theme keeps reports consistent with brand guidelines and saves formatting time, since new visuals adopt it automatically. Themes can be built in Desktop's theme customiser or edited as JSON and shared across the team as a standard.

Explain relationship cardinality in Power BI.

Data ModellingEasy

Show answer

Cardinality describes how keys match between two tables. One-to-many is the normal type, from a dimension's unique key to a fact table's repeating key. One-to-one links tables with unique keys on both sides and often means they should be merged. Many-to-many allows duplicates on both sides; it is supported but can produce confusing totals, so a bridge table is often clearer.

What are the privacy levels in Power Query and why do they cause errors?

Power Query and Data TransformationHard

Show answer

Privacy levels, Private, Organizational and Public, control whether data from one source can be sent to another source during combining, to avoid leaking sensitive data. When a query mixes sources with incompatible levels, Power Query may block it with a Formula.Firewall error or stop folding. The fix is to set correct privacy levels for each source or restructure queries so sources are combined in a staged way.

What is a star schema and why is it recommended for Power BI?

Data ModellingEasy

Show answer

A star schema has central fact tables holding numeric events, such as sales lines, linked to dimension tables describing them, such as Date, Product, Customer and Store. Power BI's engine and DAX are optimised for this shape: filters flow cleanly from dimensions to facts, measures are simpler, models compress better and reports run faster. Flat single tables or snowflakes usually need more complex DAX.

Why should you use DIVIDE instead of the division operator?

Show answer

DIVIDE(numerator, denominator, alternate) handles division by zero and blank denominators safely, returning blank or the alternate value instead of an error or infinity. It makes measures like margin percentage cleaner without IF checks. The division operator is slightly faster when you are certain the denominator is never zero, but DIVIDE is the safer default in reports.

What is the difference between COUNT, COUNTA, COUNTROWS and DISTINCTCOUNT?

Show answer

COUNT counts non-blank values in a column, COUNTA counts non-blank values of any type, COUNTROWS counts rows in a table, and DISTINCTCOUNT counts unique values in a column. For example, COUNTROWS(Sales) gives order lines while DISTINCTCOUNT(Sales[CustomerID]) gives active customers. COUNTROWS is generally preferred for counting records because its intent is clearer.

Why should you set data types correctly in Power Query?

Performance Optimisation and AdministrationEasy

Show answer

Correct data types make sorting, filtering, aggregation and relationships work properly; text-typed numbers cannot be summed and mismatched key types break relationships. Types also affect compression and model size, for example whole numbers compress better than decimals or text. Set types once near the end of a query and avoid repeated automatic Changed Type steps, which can fail when column names change.

Why can too many visuals on a page slow down a report?

Show answer

Each visual sends at least one DAX query when the page loads or when filters change, and visuals render in the browser. A page with 30 visuals, especially complex tables and custom visuals, sends many queries in parallel, competing for capacity and causing long load times. Reducing visuals, combining cards into one multi-row card or using tooltips and drill-through pages improves speed.

What is the difference between fact and dimension tables?

Data ModellingEasy

Show answer

Fact tables store measurable events at a defined grain, such as one row per order line, with numeric columns like quantity and amount and foreign keys to dimensions. Dimension tables store descriptive attributes used for filtering and grouping, such as product name, category, region or calendar month, with one row per key. Facts are usually long and narrow; dimensions are shorter and wider.

What is the difference between a calculated column and a measure?

Show answer

A calculated column is computed row by row at refresh and stored in the model, so it uses memory and can be used in slicers, filters and relationships, for example Profit = Sales[Amount] - Sales[Cost]. A measure is computed at query time in the current filter context and is not stored, for example Total Profit = SUM(Sales[Profit]). Prefer measures for aggregations and use columns only when you need to slice by the value.

What is cross-filter direction and why is bi-directional filtering risky?

Data ModellingMedium

Show answer

Cross-filter direction controls which way filters travel across a relationship. Single direction, from the one side to the many side, is the default and safest. Bi-directional lets the fact table filter the dimension too, which can be useful for slicers showing only relevant values, but it can cause ambiguous paths, unexpected results and slower queries. Prefer single direction and use CROSSFILTER inside specific measures when needed.

What is the difference between Power Query M and DAX?

Show answer

M is the language of Power Query, used at refresh time to extract, clean and shape data before it enters the model, such as merging tables or splitting columns. DAX is used after data is loaded, to define measures, calculated columns and security that evaluate in filter context at query time. As a rule, do row-level shaping in M or the source and dynamic calculations in DAX.

What do RELATED and RELATEDTABLE do?

Show answer

RELATED fetches a value from the one side of a relationship into a row context on the many side, such as RELATED('Product'[Category]) in a Sales calculated column. RELATEDTABLE goes the other way, returning the related rows from the many side for the current row, often wrapped in COUNTROWS to count a customer's orders. Both need an existing relationship and a row context.

What does the Best Practice Analyzer do?

Performance Optimisation and AdministrationMedium

Show answer

Best Practice Analyzer, in Tabular Editor and also available in Fabric notebooks through semantic link labs, scans a semantic model against a set of rules, such as avoiding bi-directional relationships, hiding foreign keys, not summarising numeric IDs, using DIVIDE, and setting format strings. It lists violations with their severity so teams can fix them before release. Rules can be customised to match team standards.

What tools do you use to analyse Power BI performance?

Performance Optimisation and AdministrationMedium

Show answer

Performance Analyzer in Desktop records how long each visual takes for DAX query, visual display and other work, and lets you copy the query. DAX Studio runs those queries with server timings and query plans and includes VertiPaq Analyzer for model size by table and column. Best Practice Analyzer in Tabular Editor flags modelling issues. In the Service, capacity metrics apps show load on Premium or Fabric capacity.

What is the difference between ALL, ALLEXCEPT and REMOVEFILTERS?

Show answer

ALL returns all rows of a table or column ignoring filters, and as a CALCULATE argument it removes filters, useful for percent of total. REMOVEFILTERS does the same removal but only as a CALCULATE modifier and reads more clearly. ALLEXCEPT removes all filters from a table except the listed columns, such as keeping the Year filter while removing others. ALLSELECTED keeps filters from outside the visual, like slicers.

What is a Power BI workspace and what roles does it have?

Show answer

A workspace is a container in the Power BI Service for semantic models, reports, dashboards, dataflows and other items that a team works on together. Roles are Admin, Member, Contributor and Viewer. Admins manage everything including access, Members can publish and share, Contributors can create and edit content but not share, and Viewers can only view. Consumers are usually given access through an app instead.

What are variables in DAX and why use them?

Show answer

VAR defines a named value inside an expression and RETURN gives the final result. Variables make measures easier to read, avoid repeating the same calculation, which improves performance, and help debugging because you can return an intermediate variable. A variable is evaluated once in the context where it is defined, so its value does not change if later CALCULATE filters are applied.

Explain the typical Power BI workflow from data to insight.

Show answer

First connect to sources such as Excel, SQL Server or SharePoint and clean the data in Power Query. Then build a data model with relationships, ideally a star schema, and write DAX measures. Next design report visuals and interactions in Desktop. Publish to a workspace in the Power BI Service, set scheduled refresh through a gateway if needed, apply row-level security and share through an app or dashboard.

What is the difference between Merge Queries and Append Queries?

Show answer

Merge is like a SQL join: it combines columns from two tables based on matching key columns, with join kinds like left outer, inner, full outer, left anti and right anti. Append stacks rows of tables with the same structure on top of each other, like a SQL UNION ALL, for example combining monthly sales files. Merge widens a table, append lengthens it.

What is the difference between drill-down and drill-through?

Show answer

Drill-down moves within the same visual through a hierarchy, such as Year to Quarter to Month, using the drill buttons. Drill-through moves the user to a separate detail page filtered by the selected item, such as right-clicking a customer to open a Customer Details page. Drill-through pages are set up by adding fields to the drill-through well and include a back button.

What is Power BI and what are its main components?

Show answer

Power BI is a business intelligence platform for connecting to data, modelling it and building interactive reports and dashboards. Main components are Power BI Desktop for building reports, Power BI Service (part of Microsoft Fabric) for publishing, sharing and refresh, Power BI Mobile apps for viewing, the on-premises data gateway for refreshing local sources, and Power BI Report Server for fully on-premises hosting of paginated and Power BI reports.

What is time intelligence in DAX?

Advanced DAX and Time IntelligenceEasy

Show answer

Time intelligence functions compare values across periods using a proper date table, for example year-to-date, same period last year, moving averages and quarter-over-quarter growth. Key functions are TOTALYTD, DATESYTD, SAMEPERIODLASTYEAR, DATEADD, PARALLELPERIOD and DATESINPERIOD. They return sets of dates used as CALCULATE filters, so the date table must be contiguous, marked as a date table and related to the facts.

What is the difference between Reference and Duplicate in Power Query?