Level 2 exam — Microsoft Power BI

Exam — fresh questions

12 questions to validate the model and DAX with Zineb, a management controller in Algiers: relationships, star schema, columns vs measures, basic DAX and the date table.
Answer everything, then click Check: your score and explanations will appear.

2.1 · Application

1. Zineb's visual shows the same total on every product category. What diagnosis?

The model is perfect but the font is too small A missing or broken relationship between Sales and Products Excel is faster on this specific point The file is too large for Power BI

Identical totals on every row signal a missing or broken relationship — diagnosis No. 1.

2.1 · Application

2. Zineb drags Sales[ProductCode] onto Products[Code]. What is the healthy default configuration?

Many-to-many cardinality and bidirectional direction No cardinality, just an alphabetical sort 1:1 cardinality and reversed single direction One-to-many cardinality (1:*) and single filter direction

The standard case is one-to-many from the reference table (the "1" side) to the facts (the "*" side), with a single filter direction.

2.1 · Arbitration

3. Her relationship refuses the 1:* cardinality. What does Zineb do?

She cleans the key on the dimension side (duplicates, blanks) in Power Query She forces bidirectional mode to bypass the block She gives up the relationship and writes VLOOKUPs She duplicates the sales rows to balance the tables

A dimension has one row per element: a key with duplicates or blanks blocks the 1:*. You clean it in Power Query.

2.2 · Recall

4. How does Zineb summarise the star schema?

Group everything into a single big table to simplify the model Arrange the report's visuals in a circle around the page canvas Facts at the centre (what you measure), dimensions around (by what you analyse), 1:* relationships Apply a harmonious colour palette to all the columns of the model

Facts = what you measure; dimensions = by what you analyse. The star makes DAX simple and performance better.

2.2 · Application

5. Zineb wants to build the Salespeople dimension from Sales. How?

Enter by hand the complete list of salespeople in a table In Power Query, duplicate Sales, keep Salesperson, remove duplicates Create a calculated column that repeats the name for each row Create a DAX measure that simply counts the sales rows

A dimension = one row per element, without duplicates: you build it in Power Query in three clicks.

2.2 · Arbitration

6. Zineb's single table still "works". What does she decide?

Keep it as is, because the star brings nothing concrete Wait for Power BI to refuse to open the file before reacting Add visuals to hide the model's slowness Reshape it into a star right now (simple DAX, better performance)

The star costs an hour now and saves days: it is the unanimous advice of practitioners and Microsoft.

2.3 · Application

7. Zineb wants a total that recalculates by city in her matrix. What does she create?

A calculated column, because it is recalculated on each filter An image of the sales table, copied onto the page A measure, calculated on the fly according to the visual's context A custom sort applied to the City column

What must react to filters is a measure: it recalculates in the visual's context, by city, by total.

2.3 · Arbitration

8. Zineb created a calculated column Margin that she merely sums. Verdict?

It should have been a measure: nothing justifies storing row by row It is perfect: a column is always better than a measure You must delete it and retype it at each refresh You must turn it into a visual so that it reacts

Reflex: measure first. A column you only ever sum should have been a measure.

2.4 · Application

9. Zineb writes Average basket to avoid a division-by-zero error. Which formula?

Average basket = [Total Revenue] / [Nb Sales] Average basket = DIVIDE([Total Revenue], [Nb Sales]) Average basket = SUM(Sales[Amount]) Average basket = COUNTROWS(Sales)

DIVIDE(numerator, denominator, [if_zero]) is armoured division: never again a division-by-zero error.

2.4 · Application

10. Zineb wants the revenue of the city of Paris alone. Which measure?

Revenue Paris = SUM(Sales[Amount]) filtered on Paris Revenue Paris = COUNTROWS(Sales) where the city equals Paris Revenue Paris = AVERAGE(Sales[Amount]) of the city of Paris Revenue Paris = CALCULATE([Total Revenue], Sales[City] = "Paris")

CALCULATE evaluates an expression while modifying the filter: "the revenue, but only for Paris".

2.5 · Recall

11. What is needed for temporal analysis (evolution, running totals, year-on-year) to work?

A large number of visuals spread over several pages Nothing special, Power BI handles dates automatically A dedicated date table, continuous, linked to the facts and marked A mandatory Premium account and a dedicated Fabric capacity

Without a dedicated, marked and linked date table, time intelligence does not work reliably.

2.5 · Arbitration

12. Zineb's months display "April, January, March" in her visuals. What does she do?

She sorts the Month column by the numeric column NumMonth She renames the months in numbers by hand in the table She reorders the bars of the visual with the mouse She turns off time intelligence in the options

A month label is sorted by its number (Column tools → Sort by column): NumMonth is the reflex.

What's next?

Score ≥ 80%: move on to Level 3 — filter context and in-depth CALCULATE with Zineb.
Score between 50% and 80%: go back over the section(s) you failed (2.1 relationships, 2.2 star, 2.3 column/measure, 2.4 DAX, 2.5 dates), reread the course, then retake the test.
Score < 50%: reread the whole level 2 while applying each 48h challenge (link two tables, build a star, the column/measure duel, the DAX foundation, the calendar) before trying again.
And after? Keep the star model, the foundation of measures and Zineb's calendar: level 3 makes them speak with filter context.