Managing credit data requires more than producing a spreadsheet total: you must preserve invoice identity, define the reporting population, calculate meaningful measures, and communicate results accurately. This guide develops those skills through 20 original written exercises and 60 concepts covering formulas, preparation, logic and lookups, PivotTables, reusable templates, Power Query, and Power BI. It follows the supplied Fall 2026 CR3-I scope as of 16 October 2026; future revisions are outside this guide. The exercises are representative learning tasks, since public evidence does not establish final-examination item types or counts. Citations establish syllabus relevance, rather than independently verifying every technical explanation. Financial examples use explicit Canadian-dollar assumptions.
What this guide covers
Practise Excel, Power Query and Power BI for CCP Credit IT I with 20 tasks and 60 detailed concepts.
Managing Credit with Information Technology I
1. Formula precedence and explicit parentheses
Excel evaluates multiplication and division before addition and subtraction unless parentheses change the grouping. A receivables formula must express the business sequence, not simply list the inputs. Distinguish a percentage applied to the original invoice from one applied to its remaining balance. Both calculations can be syntactically valid while answering different questions. References make the calculation updateable, but they do not establish the intended accounting treatment. Write the calculation in plain language first, then use parentheses wherever they clarify which amount forms the base.
Worked example: An invoice is CAD 2,000, an applied credit is CAD 200, and an internal scenario applies 3% to the remainder. =(2000-200)*3% produces CAD 54. Without parentheses, =2000-200*3% produces CAD 1,994 and answers a different calculation.
Common mistake: Assuming Excel calculates strictly from left to right can create plausible-looking amounts that apply percentages to the wrong base.
Official syllabus reference: [1]
2. Relative, absolute, and mixed references
A relative reference changes when a formula is copied; an absolute reference fixes both its column and row. Mixed references fix only one dimension. Use relative references for each invoice's own inputs, absolute references for a shared parameter, and mixed references for sensitivity grids. The dollar sign controls copying behaviour rather than making a value immutable. A user can still edit the referenced cell unless other controls prevent it. Test formulas after copying both down and across because a reference may work in one direction and fail in the other.
Worked example: Balances sit in A2:A5 and scenario rates run across B1:D1. Enter =$A2*B$1 in B2. Copying to D5 produces =$A5*D$1, combining the fifth-row balance with the rate in column D without manually editing references.
Common mistake: Locking every reference feels cautious but can make every output reuse the first invoice or the first scenario rate.
Official syllabus reference: [1]
3. Stored values versus number formats
A number format changes a cell's display without ordinarily changing its underlying value. Showing two decimal places does not round the stored number used in later calculations. Similarly, applying a currency format to numeric text does not reliably convert that text into a number. Distinguish data conversion, calculation rounding, and presentation as separate decisions. Credit reports need clear currency and date displays, but their arithmetic should use verified types. Inspect the formula bar or use a type test when a cell's appearance gives an uncertain impression of its contents.
Worked example: A calculated amount stores 12.345 but displays CAD 12.35. Multiplying the stored value by 100 produces 1,234.5, not 1,235. If the business rule requires a rounded unit amount first, use ROUND before multiplying.
Common mistake: Treating the displayed two-decimal amount as the stored value can explain apparently inconsistent totals without revealing the actual rounding rule.
Official syllabus reference: [1]
4. ROUND at the correct business stage
ROUND changes the calculated numeric value to a specified number of digits. Rounding individual items before aggregation can differ from rounding only the final total. Neither sequence is automatically correct: the calculation must follow the organization's stated transaction or reporting rule. Record that rule in a reusable template rather than relying on a number format. Avoid using arbitrary rounding to force two totals to agree before investigating their sources. In credit analysis, rounding should preserve the intended monetary precision while allowing meaningful reconciliation of any remaining difference.
Worked example: Two scenario charges each calculate to CAD 1.004. Rounding each to cents gives CAD 1.00 and a CAD 2.00 total. Adding first gives CAD 2.008, which rounds to CAD 2.01; the required process determines the correct result.
Common mistake: Rounding the final total cannot reproduce every total built from individually rounded transactions, even when both displays use cents.
Official syllabus reference: [1]
5. SUMIFS as an intersection of conditions
SUMIFS adds values only for rows meeting every supplied condition. Its sum range and criteria ranges must align with the same records. Use it for questions such as positive overdue balances belonging to a selected collector and region. Comparison operators appear in criteria strings; concatenate a cell reference when the boundary is parameterized. SUMIFS does not inherently deduplicate records, convert currencies, or establish whether credit balances belong in the measure. Those decisions must be resolved in preparation and metric definitions before the aggregation can be interpreted.
Worked example: A table contains CAD 600 and CAD 400 positive overdue items for Ana, plus a CAD 100 credit. =SUMIFS(AR[OpenCAD],AR[Collector],"Ana",AR[DaysPastDue],">0",AR[OpenCAD],">0") returns CAD 1,000 because the measure expressly excludes nonpositive balances.
Common mistake: Adding separate condition totals can double-count records; SUMIFS instead evaluates the required conditions together for each source row.
Official syllabus reference: [1]
6. COUNT, COUNTA, and COUNTIFS measure different populations
COUNT counts numeric cells, COUNTA counts nonempty cells, and COUNTIFS counts rows meeting specified conditions. Select the function according to the population being measured. Counting balances is not necessarily counting invoices because some amounts may be missing or stored as text. Counting customer identifiers counts records containing identifiers rather than distinct customers. Formula-generated empty text can also behave differently from a genuinely empty cell. Define the source grain and required completeness before interpreting a count, and reconcile unexpected differences instead of assuming the functions are interchangeable.
Worked example: Five invoice rows have identifiers, but one balance is blank and one is numeric text. COUNTA on the identifier column gives five, while COUNT on balances gives three. The difference flags preparation issues rather than proving that only three invoices exist.
Common mistake: A count of populated customer codes is a record count and can substantially exceed the number of distinct customers.
Official syllabus reference: [1]
7. SUMPRODUCT for balance-weighted measures
SUMPRODUCT multiplies corresponding array elements and sums the products. Dividing balance-times-age by total eligible balance produces balance-weighted age, where larger amounts influence the result more strongly. The numerator and denominator must use the same eligible records. Negative balances, disputed items, and missing ages require explicit treatment because they can change the interpretation substantially. A simple average answers a different question by giving each record equal influence. State whether the measure uses net balances or positive invoices, and handle a zero denominator as undefined rather than automatically zero.
Worked example: One CAD 900 invoice is 60 days overdue and one CAD 100 invoice is 10 days overdue. The weighted age is (900×60+100×10)/1,000 = 55 days. The equally weighted invoice average is 35 days, reflecting a different emphasis.
Common mistake: Averaging customer averages without their balances generally fails to reproduce the portfolio's balance-weighted age and can distort comparisons between collectors.
Official syllabus reference: [1]
8. SUBTOTAL for visible filtered records
SUBTOTAL can aggregate a filtered list while excluding rows removed by the filter. For SUM behaviour, function number 9 includes manually hidden rows, while 109 excludes them; both exclude filtered-out rows. This makes SUBTOTAL useful for an interactive review total that follows the visible population. A normal SUM still includes filtered-out values. The visible total is therefore a scoped report result rather than an independent reconciliation to the full export. Make active filters clear, and keep a full-population control total available when completeness must be checked.
Worked example: An AR table totals CAD 80,000. Filtering Collector to Noor leaves CAD 18,000 visible. =SUBTOTAL(109,AR[OpenCAD]) shows CAD 18,000, while SUM shows CAD 80,000. Both can be correct because their included populations differ under the active filter.
Common mistake: Interpreting a filtered SUBTOTAL as the entire portfolio can understate exposure even though the formula is operating correctly.
Official syllabus reference: [1]
9. Named parameters for reproducible calculations
A named cell gives a shared assumption a meaningful reference such as ReportDate or ScenarioRate. Centralizing that assumption reduces inconsistent hard-coded constants and makes a model easier to inspect. Names do not validate values, explain units, or prevent edits by themselves. Pair a parameter with its definition, expected type, permitted range where appropriate, and effective date. ReportDate is especially useful when ageing must be rerun for a historical cutoff, since a volatile current-date function would otherwise change results without any change to the invoice records.
Worked example: Set ReportDate to 30 September 2026 and reference it in every ageing formula. An invoice due 20 September is ten days past due. Reopening the workbook in October leaves the historical result unchanged until the parameter is intentionally updated.
Common mistake: Using a descriptive name does not make an assumption correct; its value, units, scope, and authorization still require explicit definition.
Official syllabus reference: [1]
10. Diagnosing formula errors before suppressing them
Excel errors communicate different failures. #N/A often signals an unavailable lookup result, #DIV/0! an invalid denominator, #VALUE! an incompatible operand, and #REF! a broken reference. Diagnose the cause before replacing the display. IFNA can target an expected unavailable lookup, whereas IFERROR catches a broader range of problems and may conceal damaged formulas. A fallback value should preserve meaning: an unknown amount is not automatically zero. Use an exception label or separate status field when an error requires investigation while keeping the rest of a report usable.
Worked example: A customer lookup returns #N/A because C419 is absent from the approved master. IFNA can display Unmapped for routing to an exception list. Replacing the result with a familiar collector would hide the missing relationship and misassign collection work.
Common mistake: Wrapping every formula in IFERROR(...,0) can turn broken references and unknown balances into credible-looking zeros that distort reports.
Official syllabus reference: [1]
11. Define the grain before analysing records
Grain states exactly what one row represents, such as one invoice at one snapshot date or one payment allocation. It determines which keys should be unique and which values can be safely added. Multiple rows per invoice may be legitimate if they represent allocations, but repeating an invoice's full balance on each allocation row creates overstatement when summed. Write the grain before selecting formulas, pivots, or joins. A business identifier may be unique at one grain and repeated at another, so duplicate checks must reflect the complete row definition.
Worked example: Invoice I77 has a CAD 1,000 balance and two payment-allocation rows. If each allocation row repeats the CAD 1,000 balance, summing that column produces CAD 2,000. Aggregate at invoice grain or separate invoice balances from payment allocations before reporting exposure.
Common mistake: Assuming every export row is a distinct invoice can inflate balances and counts when the export actually contains line-level or allocation-level records.
Official syllabus reference: [1]
12. Excel Tables provide expanding structured ranges
An Excel Table gives a rectangular dataset named columns, filter controls, and structured references. Rows added within the table generally extend calculated columns and make dependent table-based references easier to maintain. This reduces fixed-range omissions but does not automatically refresh every PivotTable or external connection. Use one header row, consistent field names, and one record per row. Avoid embedded subtotals and merged headings inside the data. Check that pasted records actually belong to the table, since content placed beyond its recognized boundary may remain outside dependent analysis.
Worked example: A formula =SUM(AR[OpenCAD]) follows the AR table as new invoice rows are added. A fixed =SUM(D2:D100) omits row 101. A PivotTable based on AR still needs refreshing before it displays the newly included records.
Common mistake: Converting a range into a Table improves range management but does not prove that new data are valid or every report is refreshed.
Official syllabus reference: [1]
13. Preserve identifiers as text
Customer codes, invoice numbers, and postal codes are identifiers rather than quantities. Store them as text when their characters, leading zeroes, or exact length carry meaning. Numeric conversion can remove leading zeroes or alter long digit strings, breaking joins and confusing distinct codes. Applying a visual zero-padding format after import does not necessarily restore the original identifier semantics. Set the import type before destructive conversion and compare against an authoritative source. Standardize both sides of a match without merging codes that the business treats as distinct.
Worked example: The master uses customer code 0073, while an import converts it to numeric 73. An exact text join may fail. Reimporting the source column as text preserves 0073 and supports a valid match without inventing missing characters.
Common mistake: Padding every short code to a fixed width is unsafe unless the source definition confirms that this normalization preserves customer identity.
Official syllabus reference: [1]
14. Date conversion requires an explicit locale
A date-looking string is not yet a reliably interpreted date. Values such as 04/05/2026 can mean different calendar dates under different regional conventions. Convert with the source's documented locale and then display a clear date format, preferably including an unambiguous year-month-day representation where appropriate. Merely changing the display cannot fix a date already interpreted incorrectly. Test ambiguous dates as well as dates whose day exceeds twelve. Keep timestamps and dates distinct when a reporting rule depends on time of day or a particular timezone.
Worked example: A supplier confirms day/month/year. Its text 04/05/2026 must become 4 May 2026, not 5 April. Testing another value, 19/05/2026, helps confirm the convention, but the source specification remains the basis for converting every row.
Common mistake: A successful date conversion only proves that the text was interpretable; it does not prove that the intended month and day were selected.
Official syllabus reference: [1]
15. Whitespace cleaning without changing business keys
Imported text can contain surrounding spaces, repeated internal spaces, or nonprinting characters that make visually similar values compare differently. TRIM handles ordinary spaces, while CLEAN removes certain nonprinting characters; neither is a universal text-normalization solution. Nonbreaking spaces may need explicit replacement. Apply cleaning consistently and preserve raw values for comparison. Internal punctuation and spacing can be meaningful in identifiers, so broad substitutions require a known field definition. Cleaning should correct transport artifacts rather than silently redefine customer identities or collapse legitimately different records.
Worked example: A collector field contains 'Mina ' in one export and 'Mina' in another. Trimming ordinary trailing spaces allows a consistent grouping. A separate customer code 'AB 12' should retain its internal space unless the code specification authorizes its removal.
Common mistake: Removing every space from every field may repair one lookup while corrupting names, addresses, or identifiers that legitimately include spaces.
Official syllabus reference: [1]
16. Duplicate detection depends on the business key
A duplicate is a repeated observation at the defined grain, not merely a repeated customer or amount. InvoiceID may require CompanyID and SnapshotDate to form a complete key. First group or count candidate keys, then inspect whether repeated rows are identical copies, legitimate detail, or conflicting versions. Removing duplicates without an explicit retention rule can discard valid exposure or preserve an outdated record. Keep evidence of excluded copies and reconcile counts and totals. A matching amount alone provides weak evidence because unrelated invoices often have the same value.
Worked example: Invoice 812 exists in both Company A and Company B. Removing duplicates by InvoiceID would lose one legitimate receivable. Using CompanyID plus InvoiceID within the selected snapshot correctly distinguishes the records and still identifies a repeated export row.
Common mistake: Repeated customer codes usually reflect multiple invoices, so deleting all but one customer row can remove substantial valid receivables.
Official syllabus reference: [1]
17. Blank, zero, and error represent different states
Zero is a known numeric value, blank may mean absent or not applicable, and an error signals a failed operation. Treating them as interchangeable can distort totals, averages, and classifications. Define missing-value rules field by field: an absent due date should not automatically become zero days overdue, and an unknown balance should not become a settled invoice. Maintain status fields where needed so reports can show both valid results and coverage gaps. Software functions differ in how they handle blanks and empty text, making explicit tests important at decision boundaries.
Worked example: Three records show CAD 0, a blank balance, and a conversion error. Only the first confirms a zero receivable. Report the other two as unresolved states and count them separately instead of declaring all three invoices fully paid.
Common mistake: Replacing missing values with zero simplifies arithmetic but may falsely imply complete information and make incomplete portfolios appear healthier.
Official syllabus reference: [1]
18. Sort whole records and disclose filters
Sorting rearranges records, while filtering changes which records are visible. A sort must move every field in each record together; sorting one detached column can assign balances to the wrong invoices without changing the total. Filters usually leave the underlying records intact, so a visible subset is not the full portfolio. Use a contiguous dataset or Excel Table, recognize the header row, and inspect active filter states before reporting. Verify selected invoice-to-balance pairs after sorting because a grand total alone cannot reveal every alignment failure.
Worked example: A collector sorts only the balance column to find the largest accounts. CAD 9,000 now appears beside the wrong customer even though the column total is unchanged. Restoring the export and sorting the complete table preserves each invoice's relationships.
Common mistake: An unchanged grand total does not prove that a sort preserved the correct balance, customer, and due date on each row.
Official syllabus reference: [1]
19. Reconcile both control totals and record coverage
A control total compares a prepared dataset with an agreed source amount, but it is only one dimension of reconciliation. Combine monetary totals with row counts, distinct-key counts, exception counts, and selected record checks. Two errors can offset financially, leaving the total unchanged. Define expected differences caused by approved exclusions or currency conversions and document them separately. Reconcile at useful subdivisions, such as company or currency, so offsetting errors are less likely to remain hidden. Reconciliation validates completeness and transformation integrity without proving every source record is correct.
Worked example: An import drops a CAD 500 invoice and duplicates another CAD 500 invoice. Its grand total still matches the export. Comparing invoice keys reveals one missing and one repeated record, demonstrating why an amount-only reconciliation is insufficient.
Common mistake: A matching monetary total is necessary for many reports but cannot establish that all and only the intended invoices were included.
Official syllabus reference: [1]
20. Nested IF and IFS require boundary ordering
Conditional classifications must assign each valid input to exactly the intended category. With ordered upper bounds, later tests inherit the fact that earlier tests failed. This permits concise ageing formulas, but incorrect ordering can make categories unreachable. Handle blanks and invalid inputs before numeric comparisons when they require distinct treatment. Test values immediately below, at, and above each boundary. Ageing bands and review levels should implement a supplied business definition rather than being treated as universal credit rules. Clear labels should preserve inclusive and exclusive endpoints.
Worked example: For Current at age at or below zero, 1–30 through day 30, and 31+ thereafter, test blank first, then <=0, then <=30. Ages 0, 1, 30, and 31 verify all transition points in the classification.
Common mistake: Testing <=60 before <=30 in a first-match formula absorbs the younger group and makes the intended 1–30 result unreachable.
Official syllabus reference: [1]
21. AND and OR express different eligibility rules
AND requires every condition to be true; OR requires at least one. This distinction matters when constructing review queues or exception flags. Translate the policy into explicit logical clauses before writing a formula, and use parentheses to make combinations clear. An overdue-and-material rule differs from an overdue-or-material rule because the latter includes large current balances and small overdue balances. Missing inputs need a separate decision rather than an accidental true or false result. Test a truth table containing records that satisfy both, one, or neither condition.
Worked example: An internal queue requires age above 30 days and balance above CAD 5,000. A CAD 8,000 current invoice fails AND but passes OR. A CAD 500 invoice at 45 days also passes OR, showing how one operator changes workload scope.
Common mistake: Substituting OR for AND can greatly expand a review population while leaving each individual comparison apparently reasonable and correctly written.
Official syllabus reference: [1]
22. Exact XLOOKUP for customer attributes
An exact lookup retrieves an attribute associated with a matching identifier, such as a collector or approved region. XLOOKUP uses exact matching by default, but the key types and characters must still align. Specify an informative not-found result rather than silently assigning a default customer attribute. Verify lookup-key uniqueness because the first returned match can conceal conflicting master records. A lookup enriches an invoice record; it does not validate the source master or prove that the retrieved assignment is current. Availability and interface details depend on the Excel environment used.
Worked example: Invoice customer C208 appears in a unique master row assigned to Ravi. =XLOOKUP([@CustomerID],Master[CustomerID],Master[Collector],"Unmapped") returns Ravi. If C208 is absent, Unmapped creates a visible exception rather than falsely routing the invoice to another collector.
Common mistake: A returned value proves a match was found, but does not establish that the master contains only one authorized record for that customer.
Official syllabus reference: [1]
23. INDEX and MATCH separate locating from returning
MATCH with match type zero locates an exact key within a range, and INDEX returns the corresponding value from another range. This separates the search position from the return column and supports layouts where the desired attribute lies to the left of the key. Search and return ranges must align row for row. Like other first-match lookups, the combination does not resolve duplicate keys. Explicit exact-match syntax is important because approximate matching can otherwise return an unintended record. Use clear ranges or table columns to reduce maintenance errors.
Worked example: Collector names occupy A2:A100 and customer codes occupy C2:C100. =INDEX(A2:A100,MATCH(F2,C2:C100,0)) retrieves the collector for the exact code in F2. Both ranges begin and end on the same rows, preserving the positional relationship.
Common mistake: Misaligned return and search ranges can produce a valid-looking collector from the wrong row without generating a formula error.
Official syllabus reference: [1]
24. Approximate lookup for minimum-threshold bands
A threshold lookup maps a numeric input to a band rather than to an identical identifier. For minimum thresholds, choose the exact threshold or the next smaller value. Keep thresholds ordered and nonoverlapping so the schedule is easy to inspect, and explicitly validate inputs below its minimum. XLOOKUP match mode -1 expresses this boundary rule; other lookup methods have their own ordering requirements. The schedule must state whether thresholds are lower or upper bounds. Test exact boundaries because choosing the next larger value implements a materially different classification.
Worked example: Internal thresholds of CAD 0, CAD 10,000, and CAD 25,000 define Routine, Enhanced, and Senior review. CAD 18,000 uses the next smaller threshold, CAD 10,000, and therefore receives Enhanced rather than Senior under this minimum-bound schedule.
Common mistake: Using approximate matching on customer identifiers can return another customer's record; numeric threshold logic does not justify approximate identity matching.
Official syllabus reference: [1]
25. IFNA preserves targeted lookup exceptions
IFNA replaces #N/A while allowing other error types to remain visible. This is useful when a missing lookup is an expected exception but a broken reference or invalid expression still needs investigation. Choose a fallback such as Unmapped that communicates the unresolved state. Do not substitute zero when the returned field is a balance unless zero is genuinely established. A separate match-status column can preserve numeric calculations while making exceptions countable. Error handling should distinguish anticipated absence from structural failure rather than making every problematic row appear successfully processed.
Worked example: A lookup for customer C550 returns #N/A because no approved region exists. IFNA displays Unmapped. If a deleted return column instead causes #REF!, the error remains visible, signalling a damaged formula rather than an ordinary missing customer mapping.
Common mistake: Broadly suppressing every lookup error can hide damaged workbook structure and make a technical failure look like a routine unmatched customer.
Official syllabus reference: [1]
26. Days past due needs a defined reporting date
Days past due compares a reporting date with a contractual due date under an explicit convention. For whole dates, ReportDate minus DueDate gives a signed day difference. MAX(0, difference) treats future-due items as zero overdue days, while retaining the signed difference supports days-until-due analysis. Neither convention should be assumed silently. Missing due dates require an exception state, and timestamps may need conversion before subtraction if fractional days are unwanted. Use a fixed reporting parameter for historical analysis so saved data produce stable results when reopened later.
Worked example: With ReportDate set to 30 September 2026, an invoice due 20 September is ten days overdue. An invoice due 5 October has a signed difference of negative five days, or zero days past due under an explicitly floored overdue measure.
Common mistake: Using today's date for an archived month-end report changes invoice ages over time and undermines comparison with the originally published figures.
Official syllabus reference: [1]
27. Composite keys must prevent collisions
A composite key combines fields needed to identify a record, such as CompanyID and InvoiceID. Simply concatenating values without separators can create collisions: different component pairs can produce the same string. Prefer matching on multiple columns where supported, or use an unambiguous, consistently typed construction with a separator that cannot occur unexpectedly. Preserve leading zeroes and define treatment of missing components. Composite keys should reflect the business grain; adding fields arbitrarily can hide duplicates rather than establish valid uniqueness. Test candidate keys against the complete dataset.
Worked example: Company 1 with invoice 234 and company 12 with invoice 34 both become 1234 under direct concatenation. Matching the two fields separately distinguishes them. A controlled representation such as 1|234 and 12|34 also avoids this particular collision.
Common mistake: Concatenation creates a string, not proof of uniqueness; ambiguous component boundaries or inconsistent normalization can still merge unrelated records.
Official syllabus reference: [1]
28. FILTER creates a dynamic exception queue
FILTER returns rows meeting an inclusion expression and can spill its results into neighbouring cells. It is useful for a live queue of overdue, unmapped, or incomplete records without deleting the source population. Multiplication can combine Boolean conditions as AND, while addition can express OR when used carefully. Provide an explicit empty-result message and keep the spill area clear. A FILTER output is a calculated view, not a substitute for source correction or controlled workflow status. Dynamic-array availability and behaviour should be checked in the actual Excel environment.
Worked example: A table has valid whole-number ages. =FILTER(AR,(AR[DaysPastDue]>30)*(AR[OpenCAD]>0),"No qualifying items") returns positive invoices older than 30 days. When an input changes, the queue recalculates, while the full table remains available for reconciliation and other measures.
Common mistake: Typing follow-up notes beside a changing spill range can associate notes with different invoices after recalculation unless records are linked by stable keys.
Official syllabus reference: [1]
29. Validate lookup coverage and uniqueness separately
Lookup quality has at least two independent dimensions: whether each required source key exists in the mapping and whether the mapping has one authorized row per key. Complete coverage does not guarantee uniqueness, and unique keys do not guarantee coverage. Count unmatched source records and repeated master keys separately. Also inspect blank returned attributes because a successful match can still yield incomplete information. These checks should occur before distributing collector assignments or regional totals. Resolving a conflict requires an authoritative retention rule rather than accepting whichever match happens to appear first.
Worked example: All 200 invoice customers exist in a master, so coverage is complete. However, customer C80 has two different collectors. A first-match lookup returns one without warning. A duplicate-key check exposes the ambiguity that the coverage check cannot detect.
Common mistake: A report with no not-found errors can still contain conflicting mappings, so lookup success alone is not a sufficient validation test.
Official syllabus reference: [1]
30. PivotTable fields define the analytical question
PivotTable Rows and Columns define categories, Values define calculations, and Filters restrict the population. Moving a field changes the question answered even when the source stays identical. For receivables by collector and ageing band, collector rows and band columns create the comparison, while a balance sum measures exposure. Begin with the business question and source grain before choosing the layout. Avoid unnecessary categorical dimensions that create a sparse, difficult report. Grand totals remain subject to aggregation rules and should be reconciled to the intended filtered or unfiltered population.
Worked example: Place Collector in Rows, AgeBand in Columns, and OpenCAD in Values as Sum. The intersection of Mina and 31–60 shows that collector's balance in that band. Moving CustomerID into Values as Count answers a separate record-count question.
Common mistake: Dropping fields into a pivot until a table looks plausible can conceal that its categories or measures answer a different question.
Official syllabus reference: [1]
31. Sum, count, and distinct count are not substitutes
A sum measures an additive numeric quantity, a count measures qualifying records or populated values, and a distinct count measures unique identities. In invoice-level data, counting CustomerID usually counts invoices with customer codes rather than customers. Distinct-count capability depends on the PivotTable's model configuration and environment; use an appropriate model or a separately deduplicated customer list when necessary. Inspect the summary function explicitly, especially when a balance field defaults to Count because of mixed types. Changing Count to Sum does not repair text amounts that should have been converted.
Worked example: Customer C10 has three invoices and C20 has two. Count of CustomerID gives five populated invoice records; distinct customer count gives two. Sum of OpenCAD gives the monetary exposure and cannot be inferred from either count without the actual balances.
Common mistake: Renaming Count of CustomerID to Customers does not make the calculation distinct and can exaggerate the size of a portfolio.
Official syllabus reference: [1]
32. Percent of total depends on its denominator scope
A PivotTable can show values as a percentage of grand total, row total, or column total. These denominators answer different questions. An ageing-band share within a collector usually uses that collector's row total; a collector's contribution to company exposure uses the relevant grand total. Active filters can change both numerator and denominator. Label the percentage clearly and test one cell manually. Percentages should use a consistent population and currency basis, and negative balances can create shares that require careful explanation rather than automatic interpretation as positive exposure proportions.
Worked example: Mina has CAD 20,000 overdue out of her CAD 50,000 total, while company exposure is CAD 200,000. Her overdue amount is 40% of her own portfolio but 10% of company exposure. Both percentages are correct under different denominators.
Common mistake: Selecting percent of grand total when the question asks for each collector's internal ageing mix produces a different measure despite familiar percentage formatting.
Official syllabus reference: [1]
33. Refresh and source expansion are separate checks
A PivotTable generally uses a stored representation of its source and must be refreshed after source changes. Refreshing does not necessarily include rows beyond a fixed source range. An Excel Table helps the source expand, but the pivot still needs an update. Verify both that new records belong to the source and that the refreshed output includes them. Check counts and totals after refresh rather than relying only on a successful command. External connections introduce another layer: the underlying query may also need updating before the pivot reflects new source data.
Worked example: A pivot uses A1:D100. An invoice added on row 101 remains absent after refresh because it lies outside the range. Changing the source to an expanding AR table and refreshing includes it, which a record-count comparison can confirm.
Common mistake: A refresh-success message confirms an update operation, not that the selected source contains every record management expects to see.
Official syllabus reference: [1]
34. Slicers expose analytical scope
Slicers provide visible controls for filtering PivotTables and related charts. They improve interpretation when selected categories are easy to see, but they can also create hidden scope changes if titles and exports omit the selection. Verify which PivotTables a slicer controls through its report connections. Multiple slicers combine to restrict the population, so clearing one does not necessarily restore the whole company. A slicer is an analytical convenience rather than a security boundary. Reports should disclose important selections and distinguish the reporting date from the refresh time.
Worked example: A Region slicer selects West and a Collector slicer selects Jia. The total describes Jia's West-region items, not all West items or Jia's entire book. Clearing only Collector leaves the West restriction active, which the report title must still communicate.
Common mistake: A chart can appear company-wide after one slicer is cleared even though another connected filter still limits its underlying population.
Official syllabus reference: [1]
35. PivotChart selection should match the comparison
A PivotChart visualizes an aggregated PivotTable result, so its meaning depends on both the aggregation and the encoding. Horizontal bars suit ranked customers with long labels; lines suit ordered time observations; stacked bars can show ageing composition across a manageable number of collectors. Too many series obscure comparisons. Use clear units and avoid three-dimensional decoration that distorts perceived magnitude. Consider whether the reader needs absolute balances or proportions before selecting ordinary versus percentage stacking. Filters and refresh requirements remain relevant because the chart inherits the pivot's selected population.
Worked example: To compare the ten largest overdue customers, sort their aggregated CAD balances descending and use horizontal bars. A line joining customer names would imply an ordered progression that does not exist, while a crowded pie would make near-equal balances difficult to compare.
Common mistake: A polished chart cannot repair an inappropriate aggregation; chart type and underlying measure must both fit the manager's question.
Official syllabus reference: [1]
36. Snapshot balances are not additive across time
A snapshot records a stock at a point in time, while a transaction records a movement. Receivables balances can usually be added across customers at one date but should not be added across complete monthly snapshots to describe current exposure. Compare selected dates, choose the latest complete snapshot, or calculate a specifically defined average balance instead. Include SnapshotDate in the row key when preserving history. A PivotTable grand total over several dates may be mathematically accurate but financially misleading. State whether the dataset contains complete snapshots, incremental changes, or transactions.
Worked example: The same portfolio has CAD 140,000 at August month end and CAD 150,000 at September month end. September exposure is CAD 150,000 and the increase is CAD 10,000. Adding both snapshots to CAD 290,000 does not measure current receivables.
Common mistake: Treating every monthly balance file as new transactions repeatedly counts continuing invoices and can dramatically overstate the amount currently outstanding.
Official syllabus reference: [1]
37. Weighted ratios must be recomputed from totals
A ratio such as overdue balance divided by total balance should generally be recalculated from its aggregated numerator and denominator. Averaging percentages gives equal weight to each displayed row and can misstate a portfolio result when row sizes differ. The same issue applies to customer averages and collection performance rates. Preserve additive components in the source so a pivot or model can calculate the ratio at the requested level. Handle zero denominators explicitly and define the treatment of credits. A total percentage should represent the selected population rather than an average of convenient subtotals.
Worked example: One region has CAD 90,000 overdue out of CAD 100,000, while another has CAD 1,000 overdue out of CAD 10,000. The simple average of 90% and 10% is 50%; the combined share is 91,000/110,000, approximately 82.73%.
Common mistake: Averaging displayed regional percentages can give a tiny portfolio the same influence as a large portfolio and materially distort the company result.
Official syllabus reference: [1]
38. Separate template inputs, calculations, and outputs
A reusable workbook benefits from clear separation between imported data, user-entered parameters, calculated fields, and presentation outputs. This makes intended edits visible and reduces accidental overwriting of formulas. Keep raw imports intact where practical and document the required schema. Calculated columns should draw from inputs rather than contain pasted answers. Outputs should identify scope, units, and reporting date. Separation alone does not guarantee correctness, but it makes dependencies inspectable and supports efficient troubleshooting when a refreshed report changes unexpectedly or a user pastes data into the wrong location.
Worked example: A workbook uses RawAR for the export, Parameters for ReportDate, Calculations for ageing, and Summary for pivots. A user updates the raw table and date without touching formulas, then compares the summary totals with the imported control totals before distribution.
Common mistake: Mixing pasted inputs and formulas in the same unlabeled column makes later updates likely to replace calculations with static values.
Official syllabus reference: [1]
39. Data validation guides entry but needs verification
Data validation can restrict expected dates, numbers, or list values and display useful input messages. It helps users enter consistent collector names and valid reporting parameters. It is not a complete data-quality guarantee because pasted or imported data and workbook settings can bypass intended entry behaviour. Pair validation with exception formulas or query checks over the entire populated dataset. Allow legitimate business states: a blanket nonnegative balance rule would reject valid credit balances. Validate against the field definition rather than against a convenient assumption about what every record should look like.
Worked example: ReportDate accepts dates, and Collector uses a controlled list. After an export is pasted, an exception check still counts collector names absent from that list. OpenCAD permits negative values because approved customer credits are valid numeric records, not entry failures.
Common mistake: A validation dropdown reduces inconsistent typing but does not prove that every imported row uses an authorized value or complete record.
Official syllabus reference: [1]
40. Worksheet protection reduces accidental formula edits
Worksheet protection can leave input cells editable while restricting changes to formula cells and layout. This is useful for preserving a reusable template's structure. Configure cell locking before applying protection, and test the tasks a normal user must perform, such as extending input data or refreshing reports. Protection is an editing control rather than a substitute for file permissions, encryption, or appropriate access management. Keep recovery information under organizational control and maintain an unambiguous template version. A protected workbook still requires calculation, data-quality, and reconciliation checks.
Worked example: Unlock the ReportDate cell and invoice input area, leave calculated ageing cells locked, and protect the sheet. A test user can change the reporting date but cannot overwrite a DaysPastDue formula while pasting. Authorized file access remains controlled separately.
Common mistake: Assuming worksheet protection secures confidential customer information confuses accidental-edit prevention with the separate controls governing who can obtain the file.
Official syllabus reference: [1]
41. Design templates for new rows and empty datasets
A reusable template must handle changing record counts, including a valid period with no eligible invoices. Use expanding tables and structured references rather than fixed last-row assumptions. Define what ratios, charts, and exception messages show when their populations are empty. Distinguish an empty authorized source from a failed import that produced no rows. A zero balance and no data are different report states. Check that formulas extend to new records and that cleared prior-period inputs do not leave stale records or outputs behind. These boundaries determine whether repeated use is reliable.
Worked example: A collection queue contains no invoices above the supplied age threshold. It displays No qualifying items and a zero qualifying count. If the source query failed, the report instead shows an incomplete refresh state rather than presenting the same empty queue as successful.
Common mistake: A blank output can mean either a legitimate empty population or a broken import, so templates need separate status and completeness indicators.
Official syllabus reference: [1]
42. Build reconciliation checks into the template
A reusable template should expose its own control results rather than require every user to recreate them. Include source row count, processed row count, CAD totals, unmatched-key count, and unresolved exceptions appropriate to the workflow. Compare values against explicit expectations and show the size of any difference. A green status should depend on defined checks, not merely on an error-free formula. Controls should remain independent enough to detect the transformations they validate; copying the same incorrect formula into both sides of a comparison creates only apparent assurance.
Worked example: An import has 420 rows. The template reports 417 valid rows and three exceptions, reconciling the population. It also compares the valid CAD subtotal with the source total while showing that unresolved amounts prevent a complete monetary reconciliation until those exceptions are resolved.
Common mistake: A control that calculates both expected and actual totals from the same flawed output may agree perfectly while missing the original processing error.
Official syllabus reference: [1]
43. Version and dependency controls preserve reproducibility
A template's results depend on its formulas, parameters, source files, connections, and sometimes external workbook links. Record a version identifier and reporting assumptions so a published result can be reproduced. Prefer controlled, inspectable dependencies over formulas linked to a colleague's temporary path. Identify which inputs are frozen for an issued report and which refresh dynamically. A saved workbook is not necessarily a frozen report if opening it updates links or volatile functions. Preserve an appropriate dated source and output under the organization's retention and access practices.
Worked example: Version 1.3 changes an ageing-band boundary under a new internal policy. The September report retains version 1.2, its ReportDate, and the approved export. An analyst can reproduce the old report without accidentally applying the later policy to historical figures.
Common mistake: Saving a workbook under a dated filename does not guarantee reproducibility when its formulas still depend on changing external files or current-date functions.
Official syllabus reference: [1]
44. Power Query stores repeatable transformation steps
Power Query connects to a source and records an ordered sequence of transformations that can be reapplied during refresh. It is suited to recurring preparation tasks such as filtering files, setting types, cleaning fields, and combining tables. The resulting output is derived from the source, so manual edits to a loaded result may be lost or disconnected from the next refresh. Make corrections in the authorized source or in an explicit transformation or mapping. Query steps improve repeatability, but source schema changes and flawed business rules still require validation.
Worked example: Each month an export needs its first two report-title rows removed, headers promoted, and balances converted to decimals. Power Query stores those steps. Refresh applies them to the new approved export without requiring the analyst to repeat the same manual worksheet edits.
Common mistake: Manually fixing a loaded query result can create a temporary appearance of correctness while leaving the underlying repeatable process unchanged.
Official syllabus reference: [1]
45. Query data types and locale affect meaning
Power Query types determine how values are parsed and processed, including text, dates, whole numbers, and decimal amounts. Automatic type detection is convenient but may misinterpret identifiers or ambiguous regional formats. Set important types deliberately and use an explicit locale when source conventions differ from the environment. Retain original fields or a raw staging query when conversion evidence is valuable. A type-conversion error is a data-quality event, not proof that the row should be dropped. Validate representative values and totals after conversion before relying on downstream joins or aggregations.
Worked example: A source uses 1.234,50 for a monetary amount under its documented convention. An explicit matching locale converts it to numeric 1234.50. Treating it under an incompatible convention can fail or produce a wrong amount, so the converted total is reconciled.
Common mistake: Automatic type detection can succeed technically while assigning the wrong meaning to a date, decimal separator, or leading-zero identifier.
Official syllabus reference: [1]
46. Filter and select columns intentionally
Power Query filters define the records retained, while column selection defines the fields available downstream. Apply scope restrictions deliberately and preserve fields needed for keys, lineage, and reconciliation. Removing irrelevant columns can simplify the query and reduce unnecessary data handling, but deleting a control field too early can make later validation impossible. Where supported, some steps may be pushed to the source; this optimization does not change their required business meaning. Document exclusions such as cancelled records or approved snapshot dates so a smaller output is understood rather than mistaken for data loss.
Worked example: A September report filters SnapshotDate to 30 September and retains InvoiceID, CustomerID, OpenCAD, and SourceFile. It removes unused commentary fields but keeps the source filename so an unexpected balance can still be traced to its approved export.
Common mistake: Removing identifiers early because they are absent from the final chart can prevent later duplicate checks, record tracing, and reconciliation.
Official syllabus reference: [1]
47. Append stacks records and aligns column names
Appending combines rows from tables with compatible meanings. Power Query aligns fields by column name rather than assuming that the second column always means the same thing. Mismatched names can create separate columns with nulls, so standardize schemas before appending. Also confirm compatible units, currencies, grain, and date coverage. Append does not establish whether datasets overlap; combining a current export with its backup can repeat every record. Complete monthly snapshots may be appended for history only if SnapshotDate is retained and later measures respect the repeated observations.
Worked example: East exports OpenCAD while West exports OpenBalance, both in CAD at invoice grain. Rename the fields consistently before appending. If left unchanged, the combined query can contain two partially populated amount columns instead of one complete monetary field.
Common mistake: Matching column positions do not guarantee compatible data, and append will not automatically recognize that a backup file repeats an existing source.
Official syllabus reference: [1]
48. Left anti joins identify invoice-level mapping exceptions
A Power Query left anti join returns rows from the left query whose join keys have no match in the right query. To build an unmapped-invoice queue, place invoices on the left and the customer master on the right, matching CustomerID. The output retains invoice detail for investigation. Its row count measures unmatched invoice records; counting distinct CustomerID values measures affected customers. These counts can differ because one customer can have several invoices. Reversing the queries instead identifies master customers absent from the invoice dataset, answering a different question.
Worked example: An invoice query contains two invoices for customer K14, three for K27, and one for K39. None of these customers appears in the master; every other invoice customer matches. A left anti join returns six invoice rows, representing three distinct customers requiring mapping review.
Common mistake: Assuming three unmatched customers means three exception rows overlooks multiple invoices per customer. Count the returned invoice records and distinct customers separately.
Official syllabus reference: [1]
49. Expansion can multiply balances after a merge
A merge can find multiple right-side matches for one left-side row. Expanding those matches creates multiple output rows, potentially repeating a full invoice balance on each. A many-to-one enrichment therefore requires a unique right-side key. Count matches or validate grouped key counts before expansion, then compare row counts and monetary totals afterward. A successful merge operation does not establish correct cardinality. Resolve conflicting mappings using their business meaning rather than deleting arbitrary duplicates or dividing repeated balances by a convenient count to force reconciliation.
Worked example: A CAD 700 invoice matches two master rows for the same customer. Expansion produces two CAD 700 rows and a CAD 1,400 total. Resolving the customer master to one authorized assignment restores a legitimate many-invoices-to-one-customer enrichment.
Common mistake: A doubled total after expansion is usually a cardinality problem to investigate, not a rounding issue to repair in the final report.
Official syllabus reference: [1]
50. Unpivot converts wide categories into rows
Unpivoting transforms selected column headings into an attribute field and their cell contents into a value field. This is useful when ageing bands or months are stored as separate columns but should act as chart categories or filters. Retain identifiers and other fixed attributes. The output grain becomes the identifier combined with the unpivoted category, so row counts increase without creating new customers or invoices. Distinguish zero values from nulls when predicting output coverage, and reconcile monetary totals before and after the transformation.
Worked example: One customer has CurrentCAD 800, PastDue1to30CAD 200, and PastDue31PlusCAD 0. Unpivoting the three nonnull numbers yields three customer-band rows totaling CAD 1,000. The zero band remains a known zero, and the customer still represents one distinct entity.
Common mistake: Counting unpivoted customer-band rows as customers overstates the customer population because each entity now appears once for each retained category.
Official syllabus reference: [1]
51. Group By creates a new aggregation grain
Power Query Group By collapses records into groups and calculates summaries such as sums or row counts. Grouping invoices by CustomerID creates customer-level data; grouping by CustomerID and SnapshotDate preserves separate historical observations. Once detail is collapsed, fields not retained through grouping are no longer available in the summarized output. Choose grouping keys according to the question and keep a separate detail query when later tracing is needed. Grouping is not deduplication: summing repeated erroneous invoices will preserve their overstatement unless duplication is resolved first.
Worked example: Three invoice rows for C90 total CAD 2,400 at September month end. Grouping by CustomerID and SnapshotDate creates one customer-date row with that amount. Keeping an invoice-detail query allows the analyst to inspect which three records contributed to the customer total.
Common mistake: Grouping duplicated invoice rows and summing their balances makes the output smaller but does not remove the duplicate exposure.
Official syllabus reference: [1]
52. Conversion exceptions need an explicit branch
A robust query distinguishes successfully transformed records from unresolved exceptions. Preserve row identifiers and raw values, then use error inspection or try-based logic to classify conversion outcomes. Create a valid-data output and an exception output with counts that reconcile to the input. Do not silently drop errors or replace unknown money with zero. An exception may block complete publication even when a valid subtotal is available. Negative numeric balances are not conversion failures merely because they require separate business interpretation in an exposure measure.
Worked example: Of 250 balance strings, 248 convert and total CAD 84,000; two contain status words. The query publishes 248 valid rows and two exceptions, reconciling all 250 inputs. CAD 84,000 is labelled a known subtotal until the missing amounts are resolved.
Common mistake: Replacing every failed conversion with zero makes incomplete data look complete and can falsely imply that unresolved invoices have no outstanding balance.
Official syllabus reference: [1]
53. Folder queries require file-selection and schema rules
Combining a folder is reliable only when the query defines which files belong in the reporting population. Filter backups, temporary files, wrong periods, and incompatible extensions before combining contents. Validate required fields and retain the source filename for lineage. A sample-file transformation assumes sufficient structural consistency; a changed column name or header layout can break or distort later refreshes. Distinguish complete snapshots from incremental files, since both can have identical schemas but different aggregation rules. Compare expected file counts, row counts, dates, and totals after refresh.
Worked example: A folder contains September.csv, September_backup.csv, and October.csv. The September process selects only the approved September file under a documented naming rule, retains its filename, and checks the snapshot date. Combining all three would mix periods and repeat September exposure.
Common mistake: Identical file structures do not mean files should be combined; backups and complete snapshots can repeat the same underlying receivables.
Official syllabus reference: [1]
54. One-to-many relationships support dimensional filtering
A basic Power BI model can separate invoice facts from a customer dimension with one unique row per CustomerID. A one-to-many relationship lets customer attributes filter related invoice records. Verify key compatibility, uniqueness on the one side, relationship activity, and intended filter direction. Duplicate dimension keys or unmatched invoice keys can produce ambiguous or incomplete analysis. Keep the invoice fact grain explicit, including SnapshotDate where history is stored. A relationship supports filtering without physically repeating every customer attribute in the fact table, but it does not repair invalid source mappings.
Worked example: Customers contains one C24 row assigned to East, while Invoices contains six C24 invoices. Selecting East filters those six records through the relationship. If C24 has conflicting dimension rows, the model must resolve that ambiguity before regional totals are trusted.
Common mistake: Creating a relationship does not establish that customer keys are unique, mappings are current, or all invoice customers have a corresponding dimension record.
Official syllabus reference: [1]
55. Measures evaluate dynamically; calculated columns store row results
A calculated column produces a value for each row during model processing, while a measure evaluates an expression under the current report context. Use columns for stable row attributes or categories and measures for interactive totals and ratios. A measure such as total open balance responds to region and date selections through filters and relationships. A calculated ageing column tied to a current-date expression updates during processing rather than continuously as viewers open the report. Choose the calculation location according to required timing, storage, and analytical behaviour rather than treating the two mechanisms as interchangeable.
Worked example: Store an invoice's approved AgeBand as a column when it belongs to a fixed snapshot. Define Total Open CAD as SUM(Invoices[OpenCAD]) as a measure. Selecting a region changes the measure's result without rewriting the stored band on every invoice.
Common mistake: Expecting a calculated column to recalculate for each viewer's slicer selection confuses stored row calculations with context-dependent report measures.
Official syllabus reference: [1]
56. CALCULATE modifies a measure's filter context
CALCULATE evaluates an expression in a modified filter context. It can add a condition such as DaysPastDue greater than zero while a region selection continues to restrict the relevant invoices. Filters applied to the same column can replace existing filters unless intersection behaviour is explicitly requested, so test interactions rather than assuming every condition simply accumulates. Start with clear base measures and build narrower measures from them. The result still depends on model relationships and date scope. CALCULATE changes analytical selection; it does not inherently create a new stored invoice field.
Worked example: Total Open CAD sums invoice balances. Overdue Open CAD = CALCULATE([Total Open CAD],Invoices[DaysPastDue]>0) evaluates that total for overdue rows. A West-region slicer still limits the result to West invoices when the customer relationship propagates the selection correctly.
Common mistake: Assuming every CALCULATE filter merely adds to every existing same-column filter can produce unexpected totals when a new condition replaces one.
Official syllabus reference: [1]
57. DIVIDE makes ratio boundaries explicit
DIVIDE calculates a numerator divided by a denominator and handles a zero or blank denominator through a defined alternate result, with blank as the default. It is useful for overdue share and other portfolio ratios. Compute the numerator and denominator as measures under compatible scope rather than averaging row-level percentages. A blank ratio can communicate that no meaningful denominator exists; replacing it with zero should require a business interpretation. Format the result as a percentage without multiplying it again by one hundred. Explain any treatment of credits that can make net-balance ratios unusual.
Worked example: Overdue Open CAD is CAD 12,000 and Total Open CAD is CAD 40,000, so DIVIDE returns 0.3, displayed as 30%. A region with no eligible total returns blank, avoiding an invented claim that its overdue share is known to be zero.
Common mistake: Multiplying a ratio by one hundred and also applying percentage formatting displays a result one hundred times larger than intended.
Official syllabus reference: [1]
58. Visual encoding should reveal magnitude and comparison
Choose a visual according to the analytical task: cards communicate a scalar, bars compare categories, lines show ordered trends, and tables support exact lookup. Use consistent units, readable labels, and sufficient contrast. Bar lengths should ordinarily use a zero baseline so magnitude comparisons remain faithful. Limit crowded categories through a disclosed ranking rule rather than hiding material data without explanation. Colour should reinforce meaning instead of carrying it alone. Every financial visual also needs a clear date and filter scope because a well-designed shape can still communicate the wrong population.
Worked example: A descending horizontal bar chart shows the five largest overdue customers in CAD, with full names and a reporting date. A nearby table provides exact balances. The title states Top Five Within Selected Region so viewers understand that the ranking changes with the region filter.
Common mistake: A dramatic truncated bar axis can exaggerate small balance differences, while colour-only status coding can make the same report difficult to interpret accessibly.
Official syllabus reference: [1]
59. Service dashboards and reports have different roles
A Power BI report supports interactive analysis across pages and visuals based on its semantic model. A service dashboard presents tiles on a single monitoring canvas and can bring selected information together. These experiences have different interaction and refresh characteristics, so test the intended consumer workflow instead of assuming a pinned tile behaves exactly like its source report page. Controlled distribution also depends on the organization's service configuration and access rights. Publication should disclose reporting date, last successful refresh, and meaningful limitations, while providing a route to appropriate detail where users need to investigate an amount.
Worked example: A manager monitors a dashboard tile showing overdue CAD, then opens the linked report to examine customers and ageing bands. Before release, the author verifies the tile's date and value against the report and tests that the manager can reach only authorized detail.
Common mistake: Publishing a dashboard tile does not establish that its audience has the intended report access or that every interactive report behaviour is preserved.
Official syllabus reference: [1]
60. Row-level security is separate from report filtering
Row-level security restricts visible model rows according to defined roles or identity-based rules. A region slicer merely selects a view and does not enforce confidentiality. Build a validated identity-to-region mapping and test its propagation through relationships. Service permissions matter because edit-capable workspace roles can have different security behaviour from ordinary viewers. Test intended users, unmapped identities, detail access, and permitted exports using the actual distribution arrangement. Hiding fields is a usability choice rather than a security boundary. Apply controlled sharing appropriate to the available configuration without assuming a particular licence entitlement.
Worked example: An East viewer should see only East invoices even after clearing every slicer and opening a detail page. A West viewer should see only West invoices, while an unmapped test identity should receive no unintended account data. The tests use ordinary consumer permissions.
Common mistake: A hidden customer field or preselected region can make a report look restricted while leaving the underlying records accessible through other authorized features.
Official syllabus reference: [1]