Power BI Online Assessment Questions &amp; Answers

Show answer

Open the workspace, select the semantic model's settings, and check Data source credentials and Gateway connection, mapping on-premises sources to a gateway. Then turn on Scheduled refresh, choose time zone, frequency and time slots, and add failure notification contacts. Pro allows up to 8 refreshes per day and Premium or Fabric capacity up to 48. Stagger refresh times to avoid source load peaks.

Write a measure that shows the selected slicer value in a title.

Advanced DAX and Time IntelligenceEasy

Show answer

Report Title = VAR r = SELECTEDVALUE('Store'[Region], "All Regions") VAR y = SELECTEDVALUE('Date'[Fiscal Year], "All Years") RETURN "Sales for " & r & " - " & y Use it in a visual title through conditional formatting, Title text, Field value. SELECTEDVALUE returns the fallback text when zero or multiple values are selected.

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.

Why should you use DIVIDE instead of the division operator?

DAX FundamentalsEasy

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.

How do you test row-level security?

Show answer

In Desktop use Modeling, View as, select a role and optionally Other user with an email to simulate dynamic RLS, and check visuals show only expected rows. In the Service open the semantic model's Security settings, select a role and use Test as role. Test with real sample users from different regions and a user with no mapping, who should see no data, and confirm totals match expectations.

What is the difference between Power Query M and DAX?

Power Query and Data TransformationEasy

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 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.

What is a disconnected table and how is it used for what-if analysis?

Show answer

A disconnected table has no relationship to the model and is used to capture a user's choice through a slicer. For example, a Discount table from 0 to 30 percent created with GENERATESERIES, or the What-if parameter feature, lets a measure read SELECTEDVALUE(Discount[Value]) and calculate projected revenue. Since it does not filter facts, the measure decides how the selection is applied.

Split a datetime column to reduce cardinality in Power Query.

Performance Optimisation and AdministrationMedium

Show answer

= Table.AddColumn(Source, "OrderDateOnly", each DateTime.Date([OrderDateTime]), type date) = Table.AddColumn(Prev, "OrderHour", each Time.Hour([OrderDateTime]), Int64.Type) Then remove the original OrderDateTime column. A datetime with seconds can have millions of unique values, while date and hour columns have only thousands and 24 values, compressing much better.

Write a measure for percentage of grand total sales.

Show answer

Sales % of Total = DIVIDE( [Total Sales], CALCULATE([Total Sales], REMOVEFILTERS('Product')) ) This gives each product's share of total across all products. Use ALLSELECTED('Product') instead if the denominator should respect slicer selections on products.

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 is a Power BI workspace and what roles does it have?

Power BI FundamentalsEasy

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 is the relationship between Power BI and Microsoft Fabric?

Power BI FundamentalsMedium

Show answer

Microsoft Fabric is Microsoft's unified analytics platform that includes data engineering, data factory, data warehousing, real-time analytics and Power BI as its reporting layer. Power BI workspaces can live on Fabric capacity and use items such as lakehouses and warehouses in OneLake. Direct Lake mode lets semantic models read Delta tables in OneLake directly without a full import, combining fresh data with near-import speed.

Write YTD, previous year and YoY growth measures.

Advanced DAX and Time IntelligenceEasy

Show answer

Sales YTD = TOTALYTD([Total Sales], 'Date'[Date], "31/3") Sales PY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date])) YoY % = VAR cur = [Total Sales] VAR py = [Sales PY] RETURN DIVIDE(cur - py, py) The "31/3" argument makes YTD follow an April to March fiscal year.

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.

A source table has duplicate customer rows with different spellings of the same name. What do you do?

Power Query and Data TransformationMedium

Show answer

For exact duplicates, Remove Duplicates on the key column. For spelling variations, use fuzzy matching in Merge Queries with a similarity threshold and transformation table, review results, and maintain a mapping table of approved matches. The best long-term fix is to use a stable customer ID from the source system and report data quality issues to the owner rather than cleaning endlessly in Power BI.

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 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.

Refresh takes 90 minutes because of heavy Power Query steps. How do you speed it up?

Power Query and Data TransformationMedium

Show answer

I would check query folding with View Native Query or the folding indicators and move non-folding steps after folding ones. Filter rows and remove unused columns as early as possible, push joins and aggregations into SQL views, avoid row-by-row custom functions over large tables, and disable load for staging queries. For large fact tables, set up incremental refresh so only recent partitions reload.

Write a measure for sales of a specific category and region.

DAX FundamentalsEasy

Show answer

Electronics North Sales = CALCULATE( [Total Sales], 'Product'[Category] = "Electronics", 'Store'[Region] = "North" ) Multiple filter arguments are combined with AND. Wrap a filter with KEEPFILTERS if it should intersect with an existing slicer selection instead of replacing it.

How do you create a measure-driven conditional colour?

Show answer

Sales Colour = VAR ach = DIVIDE([Total Sales], [Target]) RETURN SWITCH(TRUE(), ach < 0.9, "#D64550", ach < 1, "#F2A93B", "#2E9E5B") In the visual, open conditional formatting for background or font colour, choose Format style Field value and select this measure.

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 a snowflake schema and should you avoid it in Power BI?

Data ModellingMedium

Show answer

A snowflake schema normalises dimensions into several related tables, such as Product linked to Subcategory linked to Category. It saves little space in Power BI because columnar compression already handles repetition, and it adds relationships, longer filter paths and a more confusing field list. Usually it is better to flatten these into one Product dimension in Power Query, keeping the model a clean star.

How do you sort a month name column correctly?

Data ModellingEasy

Show answer

By default month names sort alphabetically, April first. In the Date table add a Month Number column, select the Month name column, then choose Column tools, Sort by column, Month Number. The same applies to weekday names and fiscal months. The sort column must have one value per name value, otherwise Power BI shows an error.

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

Visualisation and Report DesignEasy

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 does CALCULATE do?

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.

How do you control how visuals interact with each other?

Show answer

By default, selecting a data point cross-filters or cross-highlights other visuals on the page. Use Format, Edit interactions to choose filter, highlight or none for each visual pair, for example stopping a slicer from affecting a target KPI card. Slicers can also be synced across pages with the Sync slicers pane. Clear interaction design prevents confusing behaviour.

How do you build a custom tooltip page?

Show answer

Create a new page, set Page information, Allow use as tooltip on, and set Canvas settings to the Tooltip size. Add small visuals like a monthly trend line for the hovered item. On the main visual, go to Format, Tooltips, set Type to Report page and select the tooltip page. Fields in the main visual automatically filter the tooltip page on hover.

What is the difference between SELECTEDVALUE and VALUES?

Show answer

SELECTEDVALUE(column, alternate) returns the single value of a column when exactly one is visible in the filter context, otherwise the alternate or blank. VALUES(column) returns a table of distinct visible values, including blank for unmatched rows, and can be used in iterators or as a filter. Use SELECTEDVALUE for titles and parameters, and VALUES when you need to loop over the visible items.

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.

How do you build a toggle between a chart and a table using bookmarks?

Show answer

Place a chart and a table in the same position. In the Selection pane hide the table and create a bookmark named Chart View with Data unchecked so filters are not saved; then show the table, hide the chart and create Table View. Add two buttons and set each button's action to the matching bookmark. Group the visuals to keep the layout tidy.

How do you create a fiscal year column starting in April?

Data ModellingMedium

Show answer

Fiscal Year = VAR m = MONTH('Date'[Date]) VAR y = YEAR('Date'[Date]) RETURN "FY " & IF(m >= 4, y, y - 1) & "-" & RIGHT(IF(m >= 4, y + 1, y), 2) For April 2026 this returns FY 2026-27, which matches the Indian financial year. Add a fiscal month number column for sorting too.

How do you connect Power BI Desktop to a SQL Server database?

Power BI FundamentalsEasy

Show answer

Use Home, Get Data, SQL Server. Enter the server name and optionally the database, choose Import or DirectQuery, and optionally paste a native SQL query under Advanced options. Choose Windows or database authentication, then pick tables in the Navigator and select Transform Data to clean them in Power Query. For scheduled refresh of an on-premises server, an on-premises data gateway must be configured in the Service.

How do you split a full name column and clean text in Power Query?

Power Query and Data TransformationEasy

Show answer

Select the column, use Transform, Split Column by Delimiter (space, leftmost) to get first and last names. Then use Format, Trim and Clean to remove extra spaces and non-printable characters, and Capitalize Each Word for consistent case. In M: = Table.TransformColumns(Source, {{"Name", each Text.Proper(Text.Trim(_)), type text}})

What are field parameters in Power BI?

Show answer

Field parameters let report users switch which measure or dimension a visual shows using a slicer, for example choosing between Sales, Profit and Units, or switching the axis between Region and Category. They are created through Modeling, New parameter, Fields, which generates a calculated table. They reduce the number of duplicate visuals and bookmarks needed for dynamic reports.

What are the most useful table functions in DAX?

Show answer

Key table functions include FILTER, ALL, VALUES, DISTINCT, SUMMARIZE and SUMMARIZECOLUMNS for grouping, ADDCOLUMNS to add calculated columns, TOPN for top rows, CROSSJOIN, UNION, EXCEPT, INTERSECT, GENERATESERIES and TREATAS for virtual relationships. They return tables used inside iterators, CALCULATE filters or calculated tables. ADDCOLUMNS over SUMMARIZE is a common pattern for reliable grouped results.

What is conditional formatting and where can you apply it?

Visualisation and Report DesignEasy

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.

Write a dynamic rank measure for products.

Show answer

Product Rank = IF( ISINSCOPE('Product'[Product]), RANKX(ALLSELECTED('Product'[Product]), [Total Sales], , DESC, DENSE) ) ALLSELECTED ranks within the products visible after slicers, and ISINSCOPE hides the rank on total rows.

A measure shows the same value on every row of a table visual. Why?