Jhonatan Pinheiro
Loading page...
Jhonatan Pinheiro
Loading page...
Jhonatan Pinheiro
Loading page...
120 interview and real-world questions answered with working examples: Power Query and refresh, modeling, DAX evaluation context, the SQL behind the model, dashboard design, Figma and usage metrics.
This is a post for reference and revision: 120 questions that come up in interviews, in a dashboard code review and in that moment when the number does not add up and you have to find the cause. Every answer is direct and comes with a real example — no textbook definitions without application.
Every example uses the same retail model:
Casa & Construção Nordeste — 42 stores, 3 channels (physical store, e-commerce, telesales), around 4.2 million sales rows a year. Tables:
fVendas(grain = receipt line item),dProduto,dLoja,dCliente,dCalendarioandfMetas.
How to use it: read it by block (Power Query, modelling, DAX, SQL, UX/UI, process) or use it as a checklist the night before an interview. The questions marked 🔥 are the ones that come up most — and the ones that most often trip up people who only memorised the syntax.
The tool, the connection architecture and the data preparation stage.
It is the free tool where you connect, transform, model and design the report. It is not a database nor a corporate ETL: it keeps no history of its own, it does not orchestrate loads and it does not replace a data warehouse. When someone uses Power BI as the company's real repository, the symptom shows up fast — 800 MB files circulating by email with diverging numbers.
Desktop does: connect, transform (M), model, measure (DAX), design
Desktop does not: keep history, schedule loads, control corporate access
That is the job of the database/DW + the Power BI Service.Desktop is the Windows application where you build. Service (app.powerbi.com) is the cloud where you publish, schedule refreshes, share and control access. Report Server is the version installed on the company's server, for those who cannot publish to the cloud — with more limited features and a slower release cycle.
Build -> Desktop (free)
Publish -> Service (Pro/PPU/Fabric) or Report Server (on-premises)
Consume -> browser, mobile app, Teams, embedded in another systemTo build in Desktop, no: it is free. The Pro licence (or PPU/Fabric capacity) is needed to publish and share with other people in a workspace. A practical exception: with Premium/Fabric capacity, whoever only consumes can have a free licence.
Desktop free
Publishing to a workspace Pro (per user)
Consuming on Premium capacity free for the reader
Sharing a .pbix by email it works, but it is a terrible idea
(no governance, no refresh, no RLS)It is the set of tables + relationships + measures: the definition of what the numbers mean. Separating it means several teams build different reports on the same truth, via a Live Connection. Without it, each department creates its own revenue measure and the meeting becomes a debate about which spreadsheet is right.
1 semantic model "Vendas Corporativo"
<- Board report (Live Connection)
<- Commercial report (Live Connection)
<- Store report (Live Connection)
Revenue is defined ONCE. Everyone reads the same number.Import copies the data into memory — fast, with the full DAX language; it is the default for 90% of cases. DirectQuery queries the source on every interaction: use it only when the real-time requirement is real, or when the data cannot leave the source. Live Connection connects to an already published semantic model (or to Analysis Services). Dual is for small dimensions in a composite model.
The question that decides it:
"Does refreshing every hour solve the business problem?"
YES -> Import (fast, cheap, full DAX)
NO -> DirectQuery (slow, limited DAX, load on the source)
Live Connection -> reuse a corporate model that is already publishedBecause of VertiPaq, the in-memory columnar engine: it stores by column, not by row, and compresses with a dictionary — the category Tintas, repeated 4 million times, becomes a number pointing to a single entry. The practical consequence: low-cardinality columns compress very well, and high-cardinality ones (receipt ID, a timestamp with seconds) are what bloat the model.
What bloats the model (in order):
1. a high-cardinality column (CupomID, a GUID, a datetime with seconds)
2. long text columns (the product's full description)
3. columns you do not use and do not even remember importing
A trick: split date and time into two columns.
A datetime with seconds = millions of distinct values.
Date (3,650 values) + Time (1,440) compresses far better.Point at a view with the right columns and period, or write the query in the connection. Ticking the table in the list and clicking Load brings everything — 60 columns and 12 years — to answer about 24 months.
Get Data > SQL Server > Advanced options > SQL statement
SELECT DataVenda, LojaSK, ProdutoSK, Qtd, ValorLiquido, Custo
FROM dbo.vw_fato_vendas
WHERE DataVenda >= '2024-01-01';It is Power Query translating your steps into a single SQL query and letting the server run it. While it is happening, filtering 4 million rows costs almost nothing; when it breaks, Power BI downloads everything and filters on your machine. Check with a right-click on the step → View Native Query: if it is greyed out, it broke there.
Folds: filtering rows, removing columns, grouping, renaming,
merging between tables on the same server
Breaks it: Table.Buffer, a custom index column, M functions with no
SQL equivalent, merging different sources
Strategy: put ALL the steps that fold first.M runs at refresh time and defines how the data arrives: structure, type, cleaning. DAX runs at click time and defines what the number means under that filter. A classic sign of confusion: a column called Total for the Year created in Power Query — it does not change when the user filters March, and everyone concludes the dashboard is wrong.
M (Power Query) DAX
runs at refresh runs on every interaction
fixed result the result depends on the filter
prepares the table calculates the indicator
a functional language an analytical expression languageLocale. The file was generated in pt-BR (a decimal comma) and Power Query interpreted it as en-US, treating the comma as a thousands separator. It produces no visible error — just revenue a hundred times higher. Type explicitly, stating the culture, or use Using Locale in the type dialog.
= Table.TransformColumnTypes(
Fonte,
{{"ValorLiquido", type number}, {"DataVenda", type date}},
"pt-BR"
)Unpivot. Select the columns that should stay (the key) and use Unpivot Columns > Unpivot Other Columns. Choosing Other is the detail that matters: when the spreadsheet gains a new month's column, the query keeps working.
// Before: Loja | Jan | Fev | Mar (the targets spreadsheet)
// After: Loja | Mes | MetaValor
= Table.UnpivotOtherColumns(Fonte, {"Loja"}, "Mes", "MetaValor")Append stacks tables of the same structure: 2025 sales + 2026 sales, more rows. Merge is the JOIN: it brings columns from another table by the key, more columns. In a star schema, merging the dimension into the fact is nearly always a mistake — the right link is a relationship.
Append -> same columns, more rows
Merge -> same rows, more columns
Join kinds:
Left Outer = LEFT JOIN (the default, and what you want 90% of the time)
Inner = INNER JOIN
Left Anti = what exists here and does NOT exist there (great for auditing)Connect to the folder, not to each file. Power Query creates a sample function from the first file and applies it to all of them. If the cleaning rule changes, you edit the function once. It is worth filtering the extension first — a temporary Excel file (~$) in the folder breaks the refresh.
= let
Pasta = Folder.Files("\\servidor\bi\inventario"),
SoXlsx = Table.SelectRows(Pasta, each
Text.EndsWith([Name], ".xlsx")
and not Text.StartsWith([Name], "~$")),
Limpo = Table.AddColumn(SoXlsx, "Dados", each
fxLimpaInventario([Content]))
in
LimpoTo take out of the code whatever changes between environments or runs: server name, database, folder path, number of months. A server parameter lets you promote from staging to production without re-editing a single query — and in the Service it can be changed without republishing the file.
// Manage Parameters > New
pServidor : Text = "srv-bi.casaeconstrucao.local"
pBanco : Text = "DW_Vendas"
let
Fonte = Sql.Database(pServidor, pBanco)
in
FonteYou define a window: the history stays archived in partitions that are not reloaded, and only the last N days are refreshed. It requires a date/time column at the source and the parameters RangeStart and RangeEnd — with those exact names, of Date/Time type — used as a filter. A 40-minute refresh usually drops to under 2.
= Table.SelectRows(Fonte, each
[DataVenda] >= RangeStart and [DataVenda] < RangeEnd)
// Modeling > Incremental refresh
// Archive data starting 3 years before refresh date
// Incrementally refresh data starting 10 days before refresh date
Careful: the filter has to be >= and < (a half-open interval),
otherwise the row on the day boundary lands in two partitions.Nearly always because the Service cannot see the source or does not have the credential. In Desktop you use your Windows account and you are inside the network; in the Service the runner is the service itself, which needs a gateway for an on-premises source and a credential configured on the semantic model. Other frequent causes: a local file path (C:) and using sources that do not support cloud refresh.
Checklist when it only breaks in the Service:
[ ] is the source on-premises? -> gateway installed, online and with the source registered
[ ] credential configured in Semantic model settings > Data source credentials
[ ] a network path (\\servidor\pasta) instead of C:\Users\voce\...
[ ] compatible privacy levels across the sources (Organizational vs Private)8 a day on Power BI Pro and up to 48 on Premium/PPU/Fabric capacity. If the business asks for a shorter interval, the ways out are DirectQuery, refreshing via API/pipeline (which also respects the plan's limit) or rethinking the real need — in practice, nearly every board decides fine on D-1 data.
Pro 8 refreshes/day (~3 h minimum interval in practice)
Premium per user 48 refreshes/day (every 30 min)
Capacity/Fabric 48 + refresh via API/pipeline
Always ask: who decides with this number, and how often?By saving as .pbip (Power BI Project): the model and the report become text files (TMDL/JSON), so the diff shows which measure changed. With .pbix, every commit is a new binary — Git stores it, but you cannot review anything.
File > Save as > Power BI project (.pbip)
meu-painel.Dataset/model.bim <- measures, tables, relationships
meu-painel.Report/report.json <- pages, visuals, positions
git diff shows:
- Margem % = DIVIDE([Margem R$], [Faturamento Bruto])
+ Margem % = DIVIDE([Margem R$], [Faturamento Líquido])Yes, nearly always. Turned on, it creates a hidden date table for each date column in the model: wasted memory, duplicated hierarchies and unpredictable time intelligence. Turn it off and create a single dCalendario, marked as a date table.
File > Options > Current File > Data Load
[ ] Auto date/time <- untick
A model with 8 date columns:
on -> 8 hidden tables, one per column
correct -> 1 dCalendario, related and marked as a date tableIn order of impact: remove unused columns, cut the period, split a datetime into date + time, swap calculated columns for measures and avoid importing support tables that only served one check. Use DAX Studio > VertiPaq Analyzer to see, in numbers, which column is costing memory.
DAX Studio > Advanced > View Metrics
A real example (a 480 MB model):
fVendas[CupomID] 182 MB <- high cardinality, nobody used it
fVendas[DataHoraVenda] 96 MB <- became Date + Time
fVendas[ObsVendedor] 54 MB <- free text, unused in the dashboard
After removing the three: 120 MB.A measure has the calculator icon and only exists when used in a visual; a column has the column icon and takes up memory on every row of the table. Rule of thumb: if the field goes to the axis, legend or filter, it has to be a column; if it goes to the value, it should be a measure.
Axis / Legend / Slicer -> a column (dProduto[Categoria])
Values -> a measure ([Faturamento])
A common mistake: dragging fVendas[ValorLiquido] straight into Values.
It works (an implicit sum), but you lose control of formatting,
of the name and of reuse — and you cannot reference it in another measure.A native feature that lets the user swap a visual's measure or dimension through a slicer. It replaces that set of three nearly identical charts and shrinks the page. You create it in Modeling > New parameter > Fields.
// Modeling > New parameter > Fields
Métricas = {
("Faturamento", NAMEOF('Medidas'[Faturamento]), 0),
("Margem R$", NAMEOF('Medidas'[Margem R$]), 1),
("Unidades", NAMEOF('Medidas'[Unidades]), 2)
}
Drag "Métricas" onto a slicer and onto the values axis.Through the Format > Edit interactions tab: for each source visual, you define whether the target filters, highlights or ignores it. It is what avoids the irritating behaviour of clicking a bar and watching the KPIs at the top change when they should be showing the overall context.
Select the stores chart > Format > Edit interactions
KPI Faturamento -> None (keeps the total view)
Line chart -> Highlight (emphasises without hiding the rest)
Detail table -> Filter (that is the point of the click)Publish to a workspace (do not share the file), organise the content and distribute it as an app. The app is what the user should open: it has named navigation, a controlled audience and does not expose drafts. The workspace is the workbench; the app is the shop window.
Development (workspace)
-> Publish
Workspace "Vendas [PROD]"
-> Create app
App "Vendas" -> audience: the AD group "Gestores Loja"
Never: sending the .pbix by email or WhatsApp
(no refresh, no RLS, no version control).A report has pages, visuals and interaction — it is what you build in Desktop. A dashboard is the screen of pinned tiles, from one or several reports, with a single level of detail. An app is the published package grouping reports and dashboards for an audience. Day to day, most of the work is in the report.
Report pages, filters, drill, interaction (Desktop -> Service)
Dashboard pinned tiles, 1 screen, no filters (Service only)
App packages and distributes to an audience (Service only)The layer where most of the problems that later show up disguised as DAX errors are born.
It is the model with a fact table at the centre (numbers and keys) surrounded by dimensions (descriptions and hierarchies), linked by one-to-many relationships. It matters because Power BI's engine was optimised for exactly that shape: filters propagate along a single path, compression is better and the DAX stays simple. Nearly every over-complicated measure is a symptom of a wrong model.
dCalendario
|
dLoja -- fVendas -- dProduto
|
dCliente
fVendas : DataVenda, LojaSK, ProdutoSK, ClienteSK, Qtd, Valor, Custo
dimensions : the text you filter and group byA fact answers how much and grows over time (one row per event: a sale, a ticket, a movement). A dimension answers who, what, where, when and grows slowly (one row per entity). A quick test: if adding up the column means nothing — a postcode, a product code — it is a dimension attribute, even though it is numeric.
Fact: 1 row per receipt line item, 4.2 mi rows/year
Dimension: 1 row per product, 38 thousand rows, changes little
Metrics always in the fact. Attributes always in the dimension.It works for a prototype with few rows, but it charges you dearly afterwards: the product's description repeats millions of times (a large model), filters get slow, there is no way to list the product that did not sell, and any correction to the catalogue requires reloading the whole fact.
Flattened (4.2 mi rows):
... | Categoria | Marca | DescricaoProduto | Cidade | Regional | ...
repeated repeated repeated repeated
Star schema:
fVendas stores ProdutoSK (a number)
dProduto stores the description ONCE, in 38 thousand rowsIt is the meaning of a row of the fact — in our case, a receipt line item. You can always aggregate upwards (day, month, region), but never go below the grain you loaded. Loading it already aggregated by day and store solves performance today and kills the question which product drove the drop forever.
Grain = receipt line item -> answers: product, receipt, salesperson, hour
Grain = day + store -> answers: only day and store
Before aggregating at the source, ask:
"is anyone going to need to open this number up?"
If the answer is maybe, keep the fine grain.1:* is the default: one product, many sales. 1:1 is rare and nearly always indicates two tables that should be one. : solves legitimate cases (a budget by category vs sales by product), but it creates ambiguity and blank rows — prefer solving it with a bridge dimension before resorting to it.
1:* dProduto[ProdutoSK] -> fVendas[ProdutoSK] the healthy default
1:1 rare; merge the tables
*:* use a bridge:
fMetas[CategoriaSK] -> dCategoria[CategoriaSK] <- dProduto[CategoriaSK]
|
fVendasIt is the filter that propagates both ways: from the dimension to the fact and back. It solves specific cases, but it creates ambiguous paths in models with several dimensions, slows the engine down and produces totals nobody can explain. When you need the effect, prefer switching it on inside the measure, with CROSSFILTER.
// Instead of leaving the relationship bidirectional in the model:
Clientes que Compraram Tinta =
CALCULATE(
DISTINCTCOUNT( dCliente[ClienteSK] ),
dProduto[Categoria] = "Tintas",
CROSSFILTER( fVendas[ClienteSK], dCliente[ClienteSK], BOTH )
)
A local, controlled effect, documented in the measure.Because the fact's date column only has the days when there were sales, and time intelligence functions need a continuous sequence of dates covering whole years. Without that, comparisons against the previous year fail silently — and a date dimension also gives you year, quarter, week and holiday with nothing to recalculate.
dCalendario =
VAR MinData = MIN( fVendas[DataVenda] )
VAR MaxData = MAX( fVendas[DataVenda] )
RETURN
ADDCOLUMNS(
CALENDAR( DATE( YEAR(MinData), 1, 1 ), DATE( YEAR(MaxData), 12, 31 ) ),
"Ano", YEAR([Date]),
"MesNum", MONTH([Date]),
"MesNome", FORMAT([Date], "mmm"),
"AnoMes", FORMAT([Date], "yyyy-mm"),
"Trimestre", "T" & QUARTER([Date])
)Power BI starts treating that column as the official time axis: the time intelligence functions automatically remove the other filters from the date table when shifting the period, and the automatic hierarchies stop interfering. Without marking it, TOTALYTD and SAMEPERIODLASTYEAR can return a wrong result — with no warning.
Select dCalendario > Table tools > Mark as date table
Date column: Date
Requirements: unique values, no nulls, continuous and covering whole years.One active relationship (the one you use most) and another inactive one, switched on as needed with USERELATIONSHIP. The alternative — two calendar tables (role-playing dimensions) — is useful when the user needs to filter by both at once, in separate slicers.
// Active relationship: fVendas[DataVenda] -> dCalendario[Date]
// Inactive relationship: fVendas[DataEntrega] -> dCalendario[Date]
Entregas =
CALCULATE(
[Faturamento],
USERELATIONSHIP( fVendas[DataEntrega], dCalendario[Date] )
)A table with no relationships at all, used as a source of choices for the user: a simulation scenario, a metric selection, value bands. The measure reads what was selected with SELECTEDVALUE and reacts. It is the basis of every what-if in Power BI.
ReajustePreco = GENERATESERIES( 0, 0.20, 0.01 ) // 0% to 20%
Faturamento Simulado =
VAR Reajuste = SELECTEDVALUE( ReajustePreco[Valor], 0 )
RETURN
[Faturamento] * ( 1 + Reajuste )Power BI only accepts a relationship on one column. When the real key is composite (store + month, in the case of targets), create a concatenated column on both sides — preferably in SQL or in Power Query, not as a calculated column, so as not to pay for memory needlessly.
-- In SQL, on both sides:
SELECT CONCAT(LojaSK, '|', FORMAT(DataMeta, 'yyyy-MM')) AS ChaveLojaMes, ...
// In the model:
fMetas[ChaveLojaMes] *---1 dLojaMes[ChaveLojaMes]
Alternative: create the missing dimension (dLojaMes) and relate both
facts to it — cleaner and it avoids the concatenation.It is the dimension whose attribute changes over time: the Recife store changed region in 2025. Type 1 overwrites (the history disappears, everything becomes the new region). Type 2 creates a new row with a validity period, and the fact points at the version in force on the sale date — that is what preserves historical truth. The choice is a business one, not a technical one.
Type 2 in dLoja:
LojaSK | LojaID | Nome | Regional | DataIni | DataFim | Atual
17 | 042 | Recife | Norte | 2020-01-01 | 2024-12-31 | N
118 | 042 | Recife | Nordeste | 2025-01-01 | 9999-12-31 | S
The deciding question: "should the 2023 sale appear under the old
region or the current one?" The answer defines type 1 or type 2.Flatten them. Normalising dProduto into product → subcategory → category (snowflake) adds relationship hops, makes filtering slower and complicates the DAX. Dimensions are small: repeating the category name across 38 thousand rows costs nothing next to the gain in simplicity.
Snowflake (avoid):
fVendas -> dProduto -> dSubcategoria -> dCategoria
Star (prefer):
fVendas -> dProduto [SKU, Description, Subcategory, Category, Brand]
Exception: a gigantic dimension (tens of millions of rows),
where normalising starts to pay off.They are two facts at different grains; never relate them to each other. Link both to the same dimensions, at the level each one exists: the target relates to the month and to the store, the sale to the day and to the store. The measures meet in the visual, not in the model.
dCalendario --1---* fVendas (by day)
dCalendario --1---* fMetas (by the first day of the month)
dLoja --1---* both
Atingimento % = DIVIDE( [Faturamento], [Meta] )
Careful when showing it by day: the target is monthly.
Either prorate the target by working day, or only show the comparison at
the monthly level (use HASONEVALUE / ISINSCOPE to decide).Hide every key and technical column, hide raw numeric columns that have already become measures, rename everything in business language, organise the measures into display folders and set each measure's default formatting (currency, percentage, decimal places). A model with 12 visible items is usable; with 140, the user goes back to Excel.
Model delivery checklist
[ ] keys (SK) hidden
[ ] the fact's raw columns hidden (ValorLiquido, Custo)
[ ] business-language names ("Faturamento", not "vlr_liq")
[ ] measures in folders: Sales / Profitability / Comparisons / Targets
[ ] format defined per measure (R$, %, 0 or 1 decimal)
[ ] description filled in on the main measures (it shows in the tooltip)From the first SUM to the evaluation context — the block that most separates whoever memorised syntax from whoever understands the language.
DAX (Data Analysis Expressions) operates over whole tables and columns inside a filter context, not over cells. In Excel, A1+B1 always adds the same two cells; in DAX, [Faturamento] returns a different value in each cell of the visual, according to that cell's filters. That is what makes the same measure serve both the annual chart and the per-store detail.
Excel: = SUM(B2:B5000) a fixed result
DAX: Faturamento = SUM( fVendas[ValorLiquido] )
in the total -> 18,437,219
in "Recife" -> 412,980
the same formula, a different contextA calculated column is evaluated at refresh, stored row by row and takes up memory — use it when you need the value to filter, group or place on an axis. A measure is evaluated on the fly, according to the filter, and takes no memory — use it for everything that is an indicator. When in doubt, start with a measure.
// Column: serves as an axis/filter
dProduto[FaixaPreco] =
SWITCH( TRUE(),
dProduto[PrecoLista] >= 500, "Alto",
dProduto[PrecoLista] >= 150, "Medio",
"Baixo"
)
// Measure: an indicator that reacts to the filter
Margem % = DIVIDE( [Margem R$], [Faturamento] )It is the set of filters active at the moment the measure is evaluated: what comes from the visual's row and column, from the slicers, from the page and report filters, from the relationships and from any CALCULATE along the way. Each cell of a matrix has its own, and the measure is executed once per cell.
Matrix: rows = dLoja[Nome], columns = dCalendario[MesNome]
Cell "Recife" x "Mar":
dLoja[Nome] = "Recife"
dCalendario[MesNome] = "Mar"
+ the Canal slicer, if there is one
+ the page filter (e.g. Ano = 2026)
[Faturamento] runs inside that set.It is the existence of a current row. It shows up in two places: inside a calculated column (which walks the table) and inside an iterator (SUMX, AVERAGEX, FILTER). Without row context, you cannot reference a column without aggregating it — hence the classic error a single value cannot be determined for the column.
// Has row context (an iterator): OK
SUMX( fVendas, fVendas[Qtd] * fVendas[PrecoUnitario] )
// Does not: error
Medida Errada = fVendas[Qtd] * 2
// Correct without an iterator:
Medida Certa = SUM( fVendas[Qtd] ) * 2It is what happens when CALCULATE (or a measure, which already has an implicit CALCULATE) is called inside a row context: the current row becomes a filter. That is why SUMX(dLoja, [Faturamento]) works — for each store, the filter context becomes that store. It is the language's most powerful and most confusing concept.
// For each row of dLoja, [Faturamento] is filtered by that store
Lojas Acima da Meta =
COUNTROWS(
FILTER( dLoja, [Faturamento] > [Meta] )
)
// Without context transition, [Faturamento] would return
// the grand total on every row.Whenever the calculation has to happen before the sum, row by row. Price times quantity is the canonical example: adding up prices and multiplying by the sum of the quantities gives a meaningless number. SUMX walks, calculates and only then adds up.
// Right
Receita Bruta = SUMX( fVendas, fVendas[Qtd] * fVendas[PrecoUnitario] )
// Wrong (and the total even looks plausible)
Receita Errada = SUM( fVendas[Qtd] ) * SUM( fVendas[PrecoUnitario] )
If the column with the multiplication's result already exists,
SUM of that column is faster than SUMX.It evaluates an expression while modifying the filter context: it adds filters, replaces the existing ones on that column and keeps the others. On top of that, it triggers context transition when used inside a row context. Practically every non-trivial measure goes through it.
Faturamento E-commerce =
CALCULATE( [Faturamento], dLoja[Canal] = "E-commerce" )
// The filter REPLACES the one on Canal and KEEPS those on date, store, product.
// If the user has already filtered Canal = "Loja física" in the slicer,
// this measure still shows e-commerce — that is the expected behaviour.The simple filter (column = value) is syntactic sugar for a FILTER over that column's values: fast and sufficient in most cases. An explicit FILTER is necessary when the condition involves a measure or compares different columns. Mind the table you choose: FILTER(fVendas, ...) walks millions of rows.
// Simple (preferable when you can)
CALCULATE( [Faturamento], dProduto[Categoria] = "Tintas" )
// FILTER: mandatory, because it compares against a measure
CALCULATE( [Faturamento], FILTER( dLoja, [Margem %] < 0.20 ) )
// Bad: it iterates the whole fact
CALCULATE( [Faturamento], FILTER( fVendas, RELATED(dProduto[Categoria]) = "Tintas" ) )ALL/REMOVEFILTERS remove filters from the given table or column (the grand total). ALLEXCEPT removes everything except the listed columns. ALLSELECTED respects what the user chose in the slicers and ignores only the visual's own filter — it is the right one for a share within the selection.
% do Total Geral = DIVIDE([Faturamento], CALCULATE([Faturamento], ALL(dProduto)))
% dentro da Marca = DIVIDE([Faturamento], CALCULATE([Faturamento], ALLEXCEPT(dProduto, dProduto[Marca])))
% do Selecionado = DIVIDE([Faturamento], CALCULATE([Faturamento], ALLSELECTED(dProduto)))
The user selected 3 of 12 categories:
ALL -> the 3 add up to 41%
ALLSELECTED -> the 3 add up to 100%Divide the measure by itself with the dimension's filter removed. The question that defines which function to use is: should the denominator take the user's selection into account? If so, ALLSELECTED; if it is always the company total, ALL/REMOVEFILTERS.
% Participação Categoria =
VAR Atual = [Faturamento]
VAR TotalSelecionado =
CALCULATE( [Faturamento], ALLSELECTED( dProduto[Categoria] ) )
RETURN
DIVIDE( Atual, TotalSelecionado )SAMEPERIODLASTYEAR shifts the context's whole period back by a year; DATEADD allows free shifts. Always show the variation alongside the value — last year's absolute number on its own rarely helps anyone decide.
Faturamento AA =
CALCULATE( [Faturamento], SAMEPERIODLASTYEAR( dCalendario[Date] ) )
Var % AA =
VAR Anterior = [Faturamento AA]
RETURN
IF( NOT ISBLANK( Anterior ), DIVIDE( [Faturamento] - Anterior, Anterior ) )With the functions TOTALYTD, TOTALQTD and TOTALMTD, which accumulate from the start of the period to the last date in the context. If the fiscal year does not start in January, state the closing date in the third argument.
Faturamento YTD = TOTALYTD( [Faturamento], dCalendario[Date] )
Faturamento QTD = TOTALQTD( [Faturamento], dCalendario[Date] )
Faturamento MTD = TOTALMTD( [Faturamento], dCalendario[Date] )
// A fiscal year ending on 30 June
Fat YTD Fiscal = TOTALYTD( [Faturamento], dCalendario[Date], "06-30" )Three causes, in this order: the date table was not marked as a date table; it has gaps or does not cover whole years; or you are using the fact's date column instead of the calendar dimension's. A fourth, subtler one: a month filter applied on the fact stops the running total seeing the previous months.
Quick diagnosis
[ ] is dCalendario marked as a date table?
[ ] does CALENDAR cover from 01/01 of the first year to 31/12 of the last?
[ ] does the measure use dCalendario[Date], not fVendas[DataVenda]?
[ ] is the period filter on dCalendario, not on fVendas?With AVERAGEX over DATESINPERIOD, anchored on the last date in the context. It serves to take the jaggedness out of a monthly series and show the trend — in retail, without it the December spike dominates the reading of the whole chart.
Média Móvel 3M =
AVERAGEX(
DATESINPERIOD( dCalendario[Date], MAX( dCalendario[Date] ), -3, MONTH ),
[Faturamento]
)With CALCULATE plus a filter of dates less than or equal to the current one, releasing the date table's filter with ALL. Careful: without the ALL, the visual's filter limits the range and the running total restarts on every row.
Acumulado =
CALCULATE(
[Faturamento],
FILTER(
ALL( dCalendario[Date] ),
dCalendario[Date] <= MAX( dCalendario[Date] )
)
)Because the table passed as the reference is already filtered by the row's context, so each store is ranked against a table of a single store. The solution is to remove that filter with ALL (or ALLSELECTED, if you want to rank only within the user's selection).
// Wrong: 1 for everyone
Posição = RANKX( dLoja, [Faturamento] )
// Right
Posição Loja = RANKX( ALL( dLoja[Nome] ), [Faturamento], , DESC, DENSE )
// A ranking that respects the user's selection
Posição na Seleção = RANKX( ALLSELECTED( dLoja[Nome] ), [Faturamento], , DESC, DENSE )Calculate the Top N total with TOPN inside CALCULATE and get Others by difference. That way the chart stays readable (10 bars, not 42) without hiding the remaining revenue.
Fat Top 10 =
CALCULATE( [Faturamento], TOPN( 10, ALL( dLoja[Nome] ), [Faturamento], DESC ) )
Fat Outros =
CALCULATE( [Faturamento], ALL( dLoja ) ) - [Fat Top 10]A disconnected table with the options + SWITCH reading SELECTEDVALUE. Always set a default in the SELECTEDVALUE, otherwise the screen is born empty when nothing is selected — one of the most common UX mistakes in dashboards with a selector.
Métrica Escolhida =
SWITCH(
SELECTEDVALUE( Métricas[Nome], "Faturamento" ), // the default!
"Faturamento", [Faturamento],
"Margem R$", [Margem R$],
"Unidades", [Unidades],
BLANK()
)Create a text measure and bind it to the visual's title through the fx button (Format > Title > Format by field). It is what makes a screenshot sent on WhatsApp still make sense outside the report's context.
Título Página =
VAR Loja = SELECTEDVALUE( dLoja[Nome], "todas as lojas" )
VAR Periodo = SELECTEDVALUE( dCalendario[AnoMes], "o período selecionado" )
RETURN
"Faturamento de " & Loja & " em " & PeriodoUse DIVIDE, which handles a zero or blank denominator and also lets you choose the alternative result. Think about the result that makes sense for the business: for margin %, blank is more honest than zero; for target attainment, zero is usually right.
Margem % = DIVIDE( [Margem R$], [Faturamento] ) // blank if there were no sales
Atingimento % = DIVIDE( [Faturamento], [Meta], 0 ) // zero makes sense
// Avoid:
Margem Errada = [Margem R$] / [Faturamento] // Infinity / an error in the visualBecause the measure is recalculated in the total's context, not summed. That is correct and expected in ratios (margin %, average ticket) and in distinct counts. It becomes a problem when the measure has conditional logic that behaves differently in the total — then control what to show with HASONEVALUE or ISINSCOPE.
Store A: ticket 210 | Store B: ticket 190 | Total: 204
It is not 400, and it is right: the total is total revenue / total receipts.
// When the total really makes no sense:
Meta por Loja = IF( HASONEVALUE( dLoja[Nome] ), [Meta], BLANK() )
// When the total does need to be the sum of the items:
Total Correto = SUMX( VALUES( dLoja[Nome] ), [Medida com lógica] )Because a distinct count is not additive: whoever bought in January and in March is one customer in the total, and two if you add the months. It is not a bug. If you need an additive number, change the definition (new customers in the month, for instance) and explain that in the report's glossary.
Clientes Ativos = DISTINCTCOUNT( fVendas[ClienteSK] )
Jan 1,200 | Feb 1,350 | Mar 1,410 | Quarter 2,480
An additive alternative:
Clientes Novos =
CALCULATE(
DISTINCTCOUNT( fVendas[ClienteSK] ),
FILTER( dCliente, dCliente[DataPrimeiraCompra] IN VALUES( dCalendario[Date] ) )
)To evaluate an expression once and reuse it, which improves performance and readability. A detail that comes up in exams: the variable holds the value from the context where it was declared — a later CALCULATE does not change it.
Var % AA =
VAR Atual = [Faturamento]
VAR Anterior = CALCULATE( [Faturamento], SAMEPERIODLASTYEAR( dCalendario[Date] ) )
RETURN
DIVIDE( Atual - Anterior, Anterior )
// Anterior was calculated BEFORE; no later CALCULATE changes its value.IF for one condition; SWITCH(TRUE(), ...) for three or more, because it reads far better than a nested IF. In both, make sure every branch returns the same type — mixing text and numbers in one measure produces strange behaviour in the visual.
Classificação =
SWITCH( TRUE(),
[Atingimento %] >= 1, "Meta batida",
[Atingimento %] >= 0.9, "Perto",
[Atingimento %] >= 0.7, "Atenção",
"Crítico"
)RELATED brings a value from the one side of the relationship (from the sale to the product): use it in the fact's row context. RELATEDTABLE brings the table from the many side (from the product to the sales): use it in the dimension's row context, normally with an aggregator.
// In the fact, fetching the dimension's attribute
fVendas[Categoria] = RELATED( dProduto[Categoria] )
// In the dimension, counting the fact's rows
dProduto[QtdVendas] = COUNTROWS( RELATEDTABLE( fVendas ) )You cannot use a simple filter: you need FILTER over the dimension, because the condition depends on a measure evaluated per row (context transition). Filter the dimension, never the fact — the performance difference is orders of magnitude.
Fat Lojas Margem Baixa =
CALCULATE(
[Faturamento],
FILTER( ALL( dLoja[Nome] ), [Margem %] < 0.20 )
)
Qtd Lojas Margem Baixa =
COUNTROWS( FILTER( ALL( dLoja[Nome] ), [Margem %] < 0.20 ) )Create a role in Modeling > Manage roles with a DAX filter on the dimension, and assign users or groups to it in the Service. Use USERPRINCIPALNAME() to tie it to the logged-in user. Always test with View as, in Desktop, before publishing.
// Role "Gerente Loja", filter on the dLoja table:
[EmailGerente] = USERPRINCIPALNAME()
// Hierarchy (a regional manager sees every store in their region):
VAR Usuario = USERPRINCIPALNAME()
RETURN
dLoja[Regional] IN
SELECTCOLUMNS(
FILTER( dAcesso, dAcesso[Email] = Usuario ),
"R", dAcesso[Regional]
)
// Test: Modeling > View as > Gerente Loja + another userIsolate it in parts: put the intermediate measures in a table next to the suspect dimension and see at what level the number goes wrong. Tools: EVALUATE in DAX Studio, the Performance Analyzer (which shows the DAX query generated by the visual) and displaying the variables one at a time.
// In DAX Studio
EVALUATE
SUMMARIZECOLUMNS(
dLoja[Nome],
"Faturamento", [Faturamento],
"Faturamento AA", [Faturamento AA],
"Var %", [Var % AA]
)
ORDER BY [Var %] ASC
// Where the value disappears is where the cause is: a filter, a relationship or the grain.Select the measure and use the Measure tools (format, decimal places, thousands separator). Set it in the model, not visual by visual — that way it is born formatted in any report. For special cases, there is the dynamic format string.
Measure tools
Faturamento -> Currency, 0 decimals R$ 18,437,219
Margem % -> Percentage, 1 decimal 23.7%
Ticket Médio -> Currency, 2 decimals R$ 214.38
// A dynamic format string (the same measure, different currencies):
SWITCH( SELECTEDVALUE( dMoeda[Codigo] ), "BRL", "R$ #,##0", "USD", "$ #,##0" )In measures, prefer SUMMARIZE only for grouping and ADDCOLUMNS for adding calculations — aggregating inside SUMMARIZE has known context traps. SUMMARIZECOLUMNS is the function for queries (DAX Studio, virtual tables), not for everyday measures.
// The recommended pattern
VAR PorLoja =
ADDCOLUMNS(
SUMMARIZE( fVendas, dLoja[Nome] ),
"@Fat", [Faturamento]
)
RETURN
COUNTROWS( FILTER( PorLoja, [@Fat] > 500000 ) )
// The @ prefix on created columns: a convention that avoids ambiguity.Mark the working day as a column in dCalendario (accounting for weekends and the holidays table) and count with CALCULATE + COUNTROWS. Doing it in the dimension, once, is far better than recalculating it in every measure.
// A column in dCalendario
dCalendario[EhDiaUtil] =
IF(
WEEKDAY( dCalendario[Date], 2 ) <= 5
&& NOT( dCalendario[Date] IN VALUES( dFeriados[Data] ) ),
1, 0
)
// The measure
Dias Úteis = CALCULATE( COUNTROWS( dCalendario ), dCalendario[EhDiaUtil] = 1 )
Meta Diária = DIVIDE( [Meta], [Dias Úteis] )Four rules that solve most cases: filter dimensions, never the fact; avoid FILTER over large tables; use variables so you do not repeat a calculation; and prefer low-cardinality columns in filters. Measure with the Performance Analyzer — optimising by intuition usually hurts readability with no real gain.
// Slow: it iterates 4.2 million rows
CALCULATE([Faturamento], FILTER(fVendas, RELATED(dProduto[Categoria])="Tintas"))
// Fast: it filters 38 thousand rows (or uses the simple filter)
CALCULATE([Faturamento], dProduto[Categoria] = "Tintas")
Target: a DAX query under 1 s per visual.Five champions: using SUM where SUMX is needed; forgetting ALL in RANKX; thinking a ratio's total should add up; using / instead of DIVIDE; and not being able to explain the difference between ALL and ALLSELECTED. All of them are about context, not syntax — which is why memorising functions does not get you through the interview.
If you can answer this, you have cleared most of it:
1. "Explain what CALCULATE does."
2. "Why is the margin % total not the sum of the rows?"
3. "What is the difference between ALL and ALLSELECTED?"
4. "When is a calculated column better than a measure?"
5. "What is context transition?"The invisible layer that decides whether the dashboard refreshes in 2 minutes or in 2 hours — and whether the number is right.
To put together a simple report, no. To work for real, yes: it is SQL that cuts the volume at the source, that lets you audit the dashboard's number against the origin and that solves in seconds what would take minutes in Power Query. In practice, the job ad asking for Power BI and SQL together is saying you will be touching the data before it becomes a visual.
The minimum asked for in a BI interview:
SELECT / WHERE / ORDER BY / GROUP BY / HAVING
the four JOINs and why LEFT duplicates
window functions (RANK, LAG, SUM OVER)
CTEs
reading an execution plan at a basic levelIn SQL: filter, join tables, aggregate heavily and everything the server does better. In Power Query: shape the format (unpivot, types, headers), handle files and whatever does not exist in SQL. The rule is push the work as close to the source as possible — not least because SQL is versionable and testable, and Power Query steps are not.
SQL source -> filtering, JOIN, GROUP BY, windows, incremental
Power Query -> types + locale, unpivot, a folder of files, a light merge
Model (DAX) -> everything that changes with the user's filterBecause the view is a contract: business names, the right columns and the rules already applied. When the physical table is renamed or gains a column, you fix it in one place and the 30 reports keep working. It is also where the exclusions nobody should forget live — the test store, a cancelled receipt.
CREATE VIEW dbo.vw_fato_vendas AS
SELECT
v.DataVenda, v.LojaSK, v.ProdutoSK,
v.Quantidade AS Qtd,
v.ValorLiquido,
v.CustoUnitario * v.Quantidade AS Custo
FROM dbo.FatoVendasItem v
WHERE v.LojaSK <> 999
AND v.StatusCupom = 'FECHADO';WHERE filters rows before grouping; HAVING filters groups after aggregating. That is why you do not use an aggregate function in the WHERE. A performance rule: whatever can be filtered in the WHERE should be — the fewer rows that reach the GROUP BY, the better.
SELECT LojaSK, SUM(ValorLiquido) AS Faturamento
FROM dbo.vw_fato_vendas
WHERE DataVenda >= '2026-01-01' -- filters rows
GROUP BY LojaSK
HAVING SUM(ValorLiquido) > 500000; -- filters groupsINNER brings only what matches on both sides. LEFT keeps everything from the left-hand table, with null where there is no match — it is the most used in BI, because the fact has to survive a missing catalogue entry. RIGHT is the same, inverted (rare; prefer rewriting it as a LEFT). FULL brings both sides, useful for reconciliation.
-- Sales with an uncatalogued product: LEFT preserves the sale
SELECT v.DataVenda, v.ValorLiquido, COALESCE(p.Categoria, 'Sem cadastro') AS Categoria
FROM dbo.vw_fato_vendas v
LEFT JOIN dbo.dProduto p ON p.ProdutoSK = v.ProdutoSK;
-- Auditing: what exists in the fact and not in the dimension
SELECT DISTINCT v.ProdutoSK
FROM dbo.vw_fato_vendas v
LEFT JOIN dbo.dProduto p ON p.ProdutoSK = v.ProdutoSK
WHERE p.ProdutoSK IS NULL;Because the right-hand side has more than one row for the same key: every row of the fact was multiplied. It is the mistake that inflates numbers most often in BI. Typical causes: a dimension with history (SCD type 2) with no validity filter, a duplicated catalogue entry, or joining on a key that is not unique.
-- Diagnosis: is the key actually unique?
SELECT ProdutoSK, COUNT(*)
FROM dbo.dProduto
GROUP BY ProdutoSK
HAVING COUNT(*) > 1;
-- A common cause: SCD type 2 with no validity filter
JOIN dbo.dLoja l
ON l.LojaID = v.LojaID
AND v.DataVenda BETWEEN l.DataIni AND l.DataFim; -- this was missingList up front the questions the dashboard answers and aggregate at the finest grain among them. If any question mentions the product, the grain has to include the product. When in doubt, keep the fine grain and solve performance in the model — disaggregating afterwards means reloading everything.
SELECT
v.DataVenda, v.LojaSK, p.Categoria,
SUM(v.Qtd) AS Qtd,
SUM(v.ValorLiquido) AS ValorLiquido,
SUM(v.Custo) AS Custo
FROM dbo.vw_fato_vendas v
JOIN dbo.dProduto p ON p.ProdutoSK = v.ProdutoSK
GROUP BY v.DataVenda, v.LojaSK, p.Categoria;
-- 4.2 mi -> 380 thousand rows. Only do it if nobody asks for the SKU.With window functions. ROW_NUMBER numbers with no ties (good for deduplicating), RANK leaves a gap after a tie and DENSE_RANK does not. PARTITION BY defines the group within which the ranking restarts — in the example, a ranking per month.
SELECT
AnoMes, LojaSK, Faturamento,
ROW_NUMBER() OVER (PARTITION BY AnoMes ORDER BY Faturamento DESC) AS Linha,
RANK() OVER (PARTITION BY AnoMes ORDER BY Faturamento DESC) AS Posicao,
DENSE_RANK() OVER (PARTITION BY AnoMes ORDER BY Faturamento DESC) AS PosicaoDensa
FROM dbo.vw_vendas_mes_loja;With SUM() OVER and a row window. ROWS UNBOUNDED PRECEDING accumulates from the start of the partition up to the current row — restarting each year, if the year is part of the PARTITION BY.
SELECT
LojaSK, AnoMes, Faturamento,
SUM(Faturamento) OVER (
PARTITION BY LojaSK, LEFT(AnoMes, 4)
ORDER BY AnoMes
ROWS UNBOUNDED PRECEDING
) AS AcumuladoAno
FROM dbo.vw_vendas_mes_loja;With LAG shifting 12 rows within the store's partition — as long as the series is complete, with no missing months. If there are gaps, join the table to itself on a calculated year-month key, which is immune to absent rows.
SELECT
LojaSK, AnoMes, Faturamento,
LAG(Faturamento, 12) OVER (PARTITION BY LojaSK ORDER BY AnoMes) AS FatAA,
Faturamento - LAG(Faturamento, 12) OVER (PARTITION BY LojaSK ORDER BY AnoMes) AS Delta
FROM dbo.vw_vendas_mes_loja;
-- A series with gaps? Prefer the self join on a period key.It is a named query that exists only during the statement (WITH ... AS). It serves to break a long query into readable stages and for recursion (a hierarchy of managers, exploding a product structure). It is not a temporary table: it stores nothing and creates no index.
WITH VendasMes AS (
SELECT LojaSK, FORMAT(DataVenda,'yyyy-MM') AS AnoMes, SUM(ValorLiquido) AS Fat
FROM dbo.vw_fato_vendas
GROUP BY LojaSK, FORMAT(DataVenda,'yyyy-MM')
),
ComMeta AS (
SELECT v.*, m.MetaValor
FROM VendasMes v
LEFT JOIN dbo.fMetas m ON m.LojaSK = v.LojaSK AND m.AnoMes = v.AnoMes
)
SELECT *, Fat / NULLIF(MetaValor, 0) AS Atingimento
FROM ComMeta;With a recursive CTE or a numbers table. Having the calendar in the database (and not only in DAX) is better: other reports, other tools and the ETL itself start using the same definition of quarter, week and holiday.
WITH Datas AS (
SELECT CAST('2020-01-01' AS date) AS Data
UNION ALL
SELECT DATEADD(DAY, 1, Data) FROM Datas WHERE Data < '2030-12-31'
)
SELECT
Data,
YEAR(Data) AS Ano,
MONTH(Data) AS MesNum,
FORMAT(Data, 'yyyy-MM') AS AnoMes,
DATEPART(QUARTER, Data) AS Trimestre,
CASE WHEN DATEPART(WEEKDAY, Data) IN (1,7) THEN 0 ELSE 1 END AS EhDiaUtil
FROM Datas
OPTION (MAXRECURSION 0);With PIVOT or, more portably, with CASE inside conditional aggregations. For BI, though, think twice: the model prefers tall data (one row per channel), because that way a new category needs no change to the query nor to the visual. Pivoting is for a checking report, not for feeding the model.
-- Conditional aggregation (works on any database)
SELECT
LojaSK,
SUM(CASE WHEN Canal = 'Loja' THEN ValorLiquido ELSE 0 END) AS FatLoja,
SUM(CASE WHEN Canal = 'E-commerce' THEN ValorLiquido ELSE 0 END) AS FatEcom,
SUM(CASE WHEN Canal = 'Televendas' THEN ValorLiquido ELSE 0 END) AS FatTele
FROM dbo.vw_fato_vendas
GROUP BY LojaSK;
-- For the model, prefer it tall: LojaSK | Canal | ValorLiquidoNULL is neither zero nor empty: it is absence, and any comparison with it gives unknown. Use IS NULL, COALESCE for a default value and NULLIF to avoid division by zero. In a dimension, swap null for an explicit label — disappearing from the screen is worse than showing up as Sem categoria.
-- Wrong: it never returns anything
WHERE Categoria = NULL
-- Right
WHERE Categoria IS NULL
SELECT
COALESCE(NULLIF(LTRIM(RTRIM(Categoria)), ''), 'Sem categoria') AS Categoria,
ValorLiquido / NULLIF(Qtd, 0) AS PrecoMedio
FROM dbo.vw_fato_vendas;Read the execution plan looking for a table scan (table/clustered index scan) where there should be a seek, and watch the difference between estimated and actual rows — a large discrepancy is usually stale statistics. Then check the basics: a function on the filtered column, which prevents the index being used.
SET STATISTICS IO, TIME ON;
-- run the query and read the logical reads
-- Kills the index (a function on the column):
WHERE YEAR(DataVenda) = 2026
-- Uses the index (a range, the column untouched):
WHERE DataVenda >= '2026-01-01' AND DataVenda < '2027-01-01'One on the filtering column (the date), with the SELECT's columns in INCLUDE, so the server resolves everything in the index without going back to the table (a covering index). Measure before and after; too many indexes make the ETL's load slower.
CREATE NONCLUSTERED INDEX IX_FatoVendas_Data
ON dbo.FatoVendasItem (DataVenda)
INCLUDE (LojaSK, ProdutoSK, Quantidade, ValorLiquido, CustoUnitario);
-- Before: 4m12s | After: 47s (the same refresh query)Group by the business key (what should be unique) and count. Duplication in a fact usually comes from a load run twice or from a badly built JOIN in the ETL — and it is the most frequent explanation for the dashboard showing higher revenue than the source system.
SELECT CupomID, ItemSeq, COUNT(*) AS Vezes
FROM dbo.FatoVendasItem
GROUP BY CupomID, ItemSeq
HAVING COUNT(*) > 1
ORDER BY Vezes DESC;
-- A quick check of the total:
SELECT COUNT(*) AS Linhas, COUNT(DISTINCT CONCAT(CupomID,'-',ItemSeq)) AS Unicos
FROM dbo.FatoVendasItem;Run the same aggregation in the database and compare it with the visual, over the same period and with the same filters. A difference nearly always has one of these four causes: an extra filter in the view, a different period, duplication in the load, or a wrong relationship in the model leaving orphan rows out.
-- In the database
SELECT FORMAT(DataVenda,'yyyy-MM') AS AnoMes, SUM(ValorLiquido) AS Fat
FROM dbo.vw_fato_vendas
WHERE DataVenda >= '2026-01-01' AND DataVenda < '2026-04-01'
GROUP BY FORMAT(DataVenda,'yyyy-MM')
ORDER BY 1;
-- In the dashboard: a matrix of AnoMes x [Faturamento], with no slicers.
-- Keep that pair as the report's regression test.Store a watermark (the last date loaded) in a control table and process only the delta, inside a transaction. It is the same principle as Power BI's incremental refresh, only under your control — and it lets you reprocess a specific period when the source corrects a backdated entry.
BEGIN TRANSACTION;
DECLARE @UltimaCarga datetime =
(SELECT MAX(DataCarga) FROM dbo.ControleCarga WHERE Tabela = 'FatoVendasItem');
INSERT INTO dbo.FatoVendasItem (...)
SELECT ... FROM origem.Vendas WHERE DataAlteracao > @UltimaCarga;
UPDATE dbo.ControleCarga
SET DataCarga = GETDATE()
WHERE Tabela = 'FatoVendasItem';
COMMIT;The visual decisions that determine whether the dashboard is used on its own or becomes an email asking for an explanation.
It is designing for the decision, not for the screen: understanding who uses it, what question they need answered, in how much time and what they do afterwards. In practice, it shows up in three things: hierarchy (what appears first), clarity (can it be understood without a legend) and path (how you get to the detail and how you get back).
Without UX: "I put in every indicator they asked for"
With UX: "this screen answers 3 questions, in this order of importance,
and it gets to the detail in 2 clicks"
A symptom of a dashboard with no UX: the user exports to Excel
before making any decision.With the questions, written one sentence each, ordered by importance — and with who is going to use it. Only afterwards come the sketch, the visuals and the colour. Starting by dragging charts is the fastest route to a full screen that answers nothing.
A 5-line brief (fill it in with the client)
1. Who uses it: store managers and the commercial director
2. When: every Monday morning, and at month-end close
3. Questions: (a) did I hit the target? (b) what fell? (c) where do I act?
4. Decision: reallocate budget and demand an action plan from the store
5. Constraints: D-1 data, access only to their own store (RLS)Between 4 and 6 in the main block, and at most 8 to 12 visuals on the page. The limit is not aesthetic: each visual is a query, and pages with 20 visuals take a long time to load and to read. If it does not fit, they are probably two different audiences — and should be two pages.
Rule of thumb
KPIs at the top: 4 to 6
Visuals on the page: up to 12 (beyond that, the page gets slow)
Pages: one per audience/question, not one per table
If you have to scroll the page, the layout has already failed.Choose by intent, not by taste. Comparing categories: bars (horizontal, sorted by value). Evolving over time: a line. Composing a whole: a stacked bar. Relating two measures: a scatter plot. Distributing: a histogram. One number against a target: a card with the variation.
Question Visual
Which store sold the most? sorted horizontal bars
How did it evolve over the year? a line (with a moving average)
How much does each channel weigh? a 100% stacked bar
Does high margin justify volume? a scatter plot (margin x volume)
Did I hit the target? a card + variation + target indicator
Where are the stores? a map (only if geography matters)With 2 or 3 slices, yes. Beyond that, no: the eye compares angles far worse than lengths, and similar slices become indistinguishable. Sorted bars answer the same question better. And a donut with 12 slices and a side legend is, in practice, a badly drawn table.
Acceptable: Paid vs Unpaid (2 slices)
Bad: 12 product categories in a pie
Better: sorted horizontal bars, top 8 + "Others"
Never: a 3D pie. It distorts the area of the front slices.The minimum that does the job. Most of the screen should be neutral (grey), with colour reserved for what needs attention. Categorical series above 6 colours become illegible — group them into Others. And fix the meaning: if e-commerce is blue, it is blue on every page of the report.
The 60-30-10 rule
60% neutral (background, text, supporting elements)
30% supporting colour (the main series)
10% highlight (what demands action)
Categorical: up to 6 colours
Sequential: 1 hue, varying in intensity (volume)
Diverging: 2 poles + a neutral in the middle (variation vs last year)Around 8% of men have a colour vision deficiency; red vs green is precisely the most problematic pair. Prefer blue vs orange for opposition, guarantee 4.5:1 contrast on text and 3:1 on graphical elements, and never convey information by colour alone: add an arrow, a sign or a label.
Instead of green/red:
positive #2B6CB0 (blue) negative #C05621 (orange)
+ arrow and sign: v +8.2% ^ -3.1%
A quick test: take the saturation out of the screen (print it in black and white).
If the information disappeared, it depended on colour alone.Showing the screen for 5 seconds, hiding it and asking what the person saw, whether the result is good or bad and what they would do now. If they do not know, hierarchy is missing — not data. Five people are enough: when three get the same thing wrong, the problem is the design.
The questions, in this order
1. What is this screen telling you?
2. Is the result good or bad?
3. What would you do with this information?
4. What was confusing?
It costs 10 minutes per person and prevents weeks of rework.In a fixed band, always in the same place on every page: the top (horizontal) or the left-hand side. The user needs to see at a glance what is filtered — scattered filters, or filters hidden in a collapsible pane, are the main cause of people reading the wrong number without noticing.
Top (recommended for 2 to 4 filters):
[ Period v ] [ Region v ] [ Channel v ] [ Clear filters ]
Left-hand side (5+ filters): a fixed width of 240 px
Always visible: a text with the current selection
"Showing: Nordeste · E-commerce · Mar/2026"Combine three channels: a symbol (an arrow or a triangle), a sign (+/-) and colour. Whoever sees colours reads it faster; whoever does not still understands. It applies to tables too: add the icon column, do not just paint the cell's background.
A good variation format:
v -3.1% (a drop) ^ +8.2% (a rise)
Conditional formatting by icons in Power BI:
Format > Conditional formatting > Icons > Format by field
Never: just a red cell, with no number and no symbol.In the executive view, abbreviate (R$ 18.4 m): cents do not change a decision. In the working table, use the full value with 2 decimals. Numbers always right-aligned, text left-aligned, the same number of decimals throughout the column. And mind the difference between a percentage variation and a percentage point.
Card: R$ 18.4 m Table: 18,437,219.63
Percentage: 23.7% (1 decimal is enough in most cases)
Variation: +8.2% (it grew 8.2% relative to the base)
Difference: +1.4 p.p. (the margin went from 22.3% to 23.7%)
Confusing the last two is a classic mistake in a results meeting.Layers. The first screen answers what (is it good or bad); the drill answers why and where. Putting the detail on the main screen makes everything slow and hides what matters. Rule of thumb: the detail should be at most two clicks away — and with a visible back button.
Level 1 Overview KPIs + trend + ranking
Level 2 Drill-through the selected store's page
Level 3 Detail a table of receipts (exportable)
Right-click on the store's bar > Drill through > Detalhe da Loja
On the destination page: [ Back ] always visible, in the same corner.In business language, the way people speak. A visual's title should be the question or the conclusion, not the field's technical name. Measures with a clear name (Faturamento, not vlr_liq_sum) because they show up in the tooltip, in the legend and in exports.
Bad Good
"Page 1" "Visão geral"
"Sum of ValorLiquido by..." "Faturamento por loja"
"Chart 3" "Evolução mensal x ano anterior"
"med_margem_pct" "Margem %"Write an explicit message in place of the blank screen, saying what happened and what to do. In Power BI, that is solved with a text box shown by a measure or with the visual's native empty state. A blank screen reads as a broken dashboard — and becomes a support ticket.
Mensagem Vazio =
IF(
ISBLANK( [Faturamento] ),
"Nenhuma venda encontrada para os filtros selecionados. " &
"Tente ampliar o período ou limpar o filtro de canal.",
""
)
Put it in a text box with fx on the value, over the visual's area.Yes, always — it is the item that most sustains trust in the dashboard. Without it, every old number becomes a suspected error. Show the data's date, not the refresh's: they are different things when the database load is late and Power BI refreshes anyway.
Última Venda Carregada = MAX( fVendas[DataVenda] )
Último Refresh = MAX( Auditoria[DataHoraCarga] )
A footer on every page:
"Data up to 19/08/2026 · refreshed at 06:12 · source: DW_Vendas"Use the Mobile Layout (View > Mobile layout) and build a vertical version with the essentials: 2 to 4 KPIs and one simple chart. It is not the same screen shrunk — it is a curation. A wide table and a detailed map do not work on a phone, and drilling by touch needs a larger tap area.
View > Mobile layout
What goes in: the main KPIs, the trend, the top 5
What stays out: a wide table, a scatter plot, a detailed map
Touch target: at least 44 x 44 px
A real test: open it in the Power BI app, on your phone, on mobile dataFirst, understand why — most of the time the dashboard does not answer their question, or a cut they need is missing. Once that is solved, offer an official export route (a detail table or a paginated report). Banning it without solving the cause only produces a parallel spreadsheet, which is worse.
The real reasons behind the request
"I want to email it" -> an email subscription in the Service
"I need the detail" -> a drill-through page with the table
"I do my own calculation" -> the measure they need does not exist
"I do not trust the number" -> a glossary and a refresh date are missingTake out what does not inform: shadows, double borders, gradients, 3D, heavy gridlines, the legend of a single series, a redundant axis when there are already data labels. Then align everything on an 8 px grid and guarantee breathing room between blocks. A clean dashboard is not an empty one: it is the one where everything left has a function.
Before publishing, remove:
[ ] 3D effects and shadows
[ ] decorative borders and coloured backgrounds per block
[ ] heavy gridlines (leave them soft, or none)
[ ] the legend when there is a single series
[ ] the Y axis when there are already data labels
[ ] an icon that merely repeats the text beside itBefore building and after publishing: the stages that separate a delivered dashboard from a used one.
For getting it wrong cheaply. Changing a rectangle in Figma takes seconds; changing a finished dashboard takes remodelling, new measures and fresh validation. Beyond that, the sketch changes the conversation: instead of arguing about a chart's colour, the client argues about whether the question is the right one — which is what matters at this stage.
The cost of changing a layout decision
paper ~ 2 min
Figma ~ 10 to 30 min
a real dashboard ~ 2 to 5 days
A bonus: the approved wireframe becomes the project's written scope.A 1280x720 frame (the proportion of the Power BI canvas), grey boxes, an 8 px grid and, inside each box, the question it answers. No colour and no real data: low fidelity stops the discussion drifting into aesthetics too early.
Frame 1280 x 720
[ band ] title + filters h 72
[ 4 boxes ] KPIs h 120
[ 2 boxes ] trend | ranking h 260
[ 1 box ] detail table h 200
Write in the box: "Which store fell the most?" — not "bar chart".By working in the same proportion and on the same grid: Power BI accepts a numeric position and size per visual, so Figma's X, Y, width and height become values in the formatting pane. Colours and fonts go through the JSON theme, and the wireframe's labels should be the real measure names.
Figma Power BI
frame 1280 x 720 -> View > Page size > 16:9
X/Y/W/H on 8 px -> Format > General > Position and size
colour styles -> JSON theme (View > Themes > Browse)
KPI component -> group of visuals copied between pages
label "Margem %" -> the measure name [Margem %]A set of decisions made once and reused: a palette with meaning, a typographic scale, spacing, number formats, the KPI card's pattern and interaction rules. The gain shows up on the fifth dashboard — they all look like the same product, and a rebrand does not require repainting 40 visuals.
What to document (one page is enough)
Colours 3 categorical + sequential + diverging + neutrals
Typography title 14 / label 10 / KPI 28 (Segoe UI)
Spacing an 8 px grid, at least 16 of breathing room between blocks
Numbers abbreviated R$ on the KPI, 2 decimals in the table, % with 1
Patterns KPI = value + variation (arrow, sign, colour)
Interaction a slicer filters; a click highlights; detail in a drill-throughWrite a file with the colours and text classes and import it in View > Themes > Browse for themes. It then applies to every new visual and is versionable in Git — unlike formatting done visual by visual, which is lost on the first new page.
{
"name": "Casa & Construção 2026",
"dataColors": ["#2B6CB0", "#2C7A7B", "#6B46C1", "#B7791F", "#B23A48"],
"background": "#FFFFFF",
"foreground": "#1A202C",
"tableAccent": "#2B6CB0",
"textClasses": {
"title": { "fontFace": "Segoe UI Semibold", "fontSize": 14, "color": "#1A202C" },
"label": { "fontFace": "Segoe UI", "fontSize": 10, "color": "#4A5568" }
}
}It is measuring the behaviour of whoever uses the product in order to decide what to change — instead of deciding by opinion. Applied to BI: how many open it, how many come back, which pages get used, how long until the first filter, where people get stuck. You already build dashboards for other people to measure the business; here you do the same with your own dashboard.
Data sources
Quantitative: usage metrics in the Service (visits, pages, users)
Qualitative: usability testing, interviews, observation
Decision: cross the two — the number says WHERE, the conversation says WHYAdoption (unique users over the target audience), retention (came back the following week), frequency, depth (pages per session) and time to first filter. Retention is the one that matters most: every dashboard gets visits at launch, because curiosity brings them.
Adoption > 60% of the audience in 30 days
Weekly retention > 40%
Frequency depends on the rhythm of the decision (weekly, daily)
Depth is the detail used, or only the top?
Time to filter > 30 s indicates the user did not work out what to do
A page with 0 visits in 60 days: remove it or investigate.Five people, 15 minutes each, four tasks. Give them a task, not a question, time it and stay quiet — the urge to help is what most ruins the test. You will watch someone look for the filter in three places, and that is worth more than an hour of requirements meeting.
T1. What was March's revenue? expected < 10 s
T2. Which store fell the most against last year? expected < 30 s
T3. In that store, which category drove the drop? expected < 45 s
T4. Export the list of stores below target. expected < 30 s
Record: time, wrong clicks, hesitation, what the person said.Separate what blocks the task from what is a preference. A blocker goes first; a preference goes in if it is cheap or if it comes up repeatedly. An isolated request from an influential user is not a priority — three people getting stuck at the same point is.
A simple matrix
Many users Few users
Blocks do it now do it later
Annoys do it later note it and observe
A useful sentence in the meeting: "does that stop you completing the task,
or is it a display preference?"Show the process, not just the pretty screen: the business question, the model (star schema), two or three measures that required a decision, the before and after of the layout and the performance number. Whoever hires for dashboards has seen too many pretty screens; what stands out is being able to explain why each choice was made.
A 5-minute script (it works in an interview and in a portfolio)
1. Problem: "42 stores, a weekly budget decision, data only in Excel"
2. Model: the star schema diagram (why facts and dimensions)
3. Measure: one that required context (ALLSELECTED, ranking, targets)
4. Design: before and after, and what the test with 5 people changed
5. Result: refresh 38 min -> 6 min; the page in 2.4 s; 78% adoption