Free JYnapse Knowledge Base · Cheat Sheet

Power BI & DAX Cheat Sheet

From raw file to a dashboard people trust — the Power Query, data-model and DAX moves that cover most business reporting.

For Analysts · finance · ops · BI builders Level Beginner → Intermediate Languages EN · BM · 中文 on request

1 The workflow

1. Get data — connect to Excel, SQL, web, folder.
2. Power Query — clean & shape (Transform Data).
3. Model — relate tables, add measures.
4. Report — drag fields onto visuals.
5. Publish — to the Power BI Service, schedule refresh.

Clean in Power Query, calculate in DAX. Don't do cleaning with DAX or you'll regret it later.

2 Power Query — clean

Each click is a recorded step you can undo/reorder (Applied Steps panel).

Use First Row as Headers	Promote headers
Change Type	Set column types
Remove / Split Columns	Trim & separate
Replace Values	Fix bad entries
Unpivot Columns	Wide → tidy long
Merge / Append	Join / stack queries

Set data types early — most refresh errors are a type surprise.

3 Model like a star

Aim for a star schema: one central fact table (transactions) linked to dimension tables (date, product, customer).

Relationships: usually one-to-many, dimension → fact.
Always add a dedicated Date table and mark it as such.
Avoid many-to-many and bidirectional filters unless you must.

A clean star model is 80% of why a report is fast and correct.

4 Measures vs columns

Calculated column	Row-by-row, stored, filter/axis
Measure	Aggregates on the fly, respects filters

Rule of thumb: if it's a number you'll sum or average → measure. If it's a category/label you'll slice by → column.

Total Sales = SUM( Sales[Amount] )
Order Count = COUNTROWS( Sales )

5 CALCULATE — the heart of DAX

Changes the filter context a measure runs in.

Sales KL =
CALCULATE( [Total Sales],
           Store[City] = "KL" )

Sales (All Products) =
CALCULATE( [Total Sales],
           ALL( Product ) )      -- ignore filters

Master CALCULATE + filter context and the rest of DAX falls into place.

6 Iterators & core functions

Revenue = SUMX( Sales,
              Sales[Qty] * Sales[Price] )

Avg Basket = AVERAGEX(
   VALUES(Sales[OrderID]), [Revenue] )

Margin % = DIVIDE([Profit],[Revenue])  -- safe ÷

SUMX / AVERAGEX	Row-wise then aggregate
DIVIDE(a,b,0)	Division, no error
IF / SWITCH	Conditional logic
RELATED	Pull from linked table

7 Time intelligence

Sales YTD =
TOTALYTD( [Total Sales], 'Date'[Date] )

Sales LY =
CALCULATE( [Total Sales],
   SAMEPERIODLASTYEAR('Date'[Date]) )

YoY % =
DIVIDE( [Total Sales]-[Sales LY], [Sales LY] )

These need a proper, continuous Date table marked with Mark as Date Table. Without it, they silently misbehave.

8 Measures you'll reuse

% of Total =
DIVIDE( [Total Sales],
   CALCULATE([Total Sales], ALL(Product)) )

Running Total =
CALCULATE( [Total Sales],
   FILTER( ALL('Date'),
     'Date'[Date] <= MAX('Date'[Date]) ) )

Distinct Customers =
DISTINCTCOUNT( Sales[CustomerID] )

9 Visuals & interactivity

Slicers for click-to-filter; Cards for headline KPIs.
Visuals cross-filter each other by default — click a bar, the page reacts.
Drill-through pages for detail behind a summary.
Bookmarks + buttons for guided navigation.
Set an explicit sort and number format on every visual.

10 Publish & refresh

Publish to a Workspace in the Power BI Service.
On-prem source? Install the Data Gateway for scheduled refresh.
Schedule refresh (up to 8×/day on Pro) so numbers stay current.
Share via an App; control access with workspace roles.
Keep the .pbix in version control / a dated backup.

If a refresh fails, it's almost always credentials or a changed column name.

JYnapse Knowledge Base · Free to read and print. Applies to Power BI Desktop & Service. Available in English, Bahasa Melayu and 中文 on request.