Power BI Quick Practice — 10 LinkedIn Posts

Power BI Quick Practice #01

🟢 Conditional Column

Power BI Quick Practice #01: Conditional Column

Not every Power BI problem needs DAX.

Sometimes the best solution is Power Query.

Business problem:
You have a CustomerAge column and you need to create age groups for reporting.

Sample data:

CustomerName | CustomerAge
Ana          | 28
Luis         | 36
Maria        | 47
John         | 59

Expected result:

CustomerName | CustomerAge | AgeBreakdown
Ana          | 28          | 18-34
Luis         | 36          | 35-44
Maria        | 47          | 45-54
John         | 59          | 55+

Logic:
If CustomerAge >= 55 → 55+
Else if CustomerAge >= 45 → 45-54
Else if CustomerAge >= 35 → 35-44
Else → 18-34

Best place to do it:
Power Query → Add Conditional Column

Why Power Query?
Because this is row-by-row classification.
It does not need filter context.
It does not need to change dynamically in a visual.
It is part of data preparation.

Use this pattern for:
• age groups
• risk bands
• score ranges
• status labels
• business classifications

Common mistake:
Creating too many DAX calculated columns for logic that should be handled before the data reaches the model.

Key idea:
Power BI is not only about knowing formulas.
It is about knowing where the logic belongs.

More practical resources:
https://jobaqui.com
https://hialeahoficios.com
https://miamioficios.com

#PowerBI
#DAX
#CalculatedTables
#DataModeling
#BusinessIntelligence
#DataAnalytics
#LearningByDoing

Power BI Quick Practice #02

🟢 Extract Numbers from Text

Power BI Quick Practice #02: Extract Numbers from Text

Real-world data is messy.

Sometimes the ID you need is hidden inside a text field.

Business problem:
You receive usernames that contain letters, symbols, and numbers.
You only need the numeric ID.

Sample data:

quiz_username
student_4589_final
user-1024
ID:7755-A
test_0098_done

Expected result:

quiz_username        | ExtractedNumber
student_4589_final   | 4589
user-1024            | 1024
ID:7755-A            | 7755
test_0098_done       | 0098

Power Query M:

Text.Select([quiz_username], {"0".."9"})

What it does:
It keeps only characters from 0 to 9 and removes everything else.

Useful for:
• employee IDs
• student IDs
• usernames
• file names
• mixed business codes
• hidden numeric keys

Why it matters:
This transformation becomes repeatable.
You define the rule once.
Power Query applies it every refresh.

Common mistake:
Cleaning this manually in Excel before loading the data.

Better approach:
Let Power Query do the cleaning.

Small formula.
Big practical value.

More practical resources:
https://jobaqui.com
https://hialeahoficios.com
https://miamioficios.com

#PowerBI
#DAX
#CalculatedTables
#DataModeling
#BusinessIntelligence
#DataAnalytics
#LearningByDoing

Power BI Quick Practice #03

🔵 DIVIDE()

Power BI Quick Practice #03: DIVIDE()

In DAX, direct division may work.

But in business reports, safer measures matter.

Business problem:
You need to calculate sales per customer.

Sample data:

CustomerKey | Quantity | Net Price
C001        | 2        | 50
C002        | 1        | 80
C001        | 3        | 40
C003        | 1        | 100

Step 1:
Calculate total sales row by row.

2 × 50  = 100
1 × 80  = 80
3 × 40  = 120
1 × 100 = 100

Total Sales = 400

Step 2:
Count unique customers.

C001
C002
C003

Unique Customers = 3

Expected result:

Sales Per Customer = 400 / 3 = 133.33

DAX:

SalesPerCustomer =
DIVIDE(
    SUMX(Sales, Sales[Quantity] * Sales[Net Price]),
    DISTINCTCOUNT(Sales[CustomerKey])
)

Why use DIVIDE() instead of / ?

Because sometimes the denominator can be zero or blank in the current filter context.

Examples:
• no customers selected
• empty segment
• filtered visual with no records
• incomplete data

DIVIDE() helps protect the measure.

Common mistake:
Using direct division everywhere and then wondering why some visuals return errors or unexpected results.

Key idea:
Good DAX is not only about getting the number.
Good DAX is about creating measures that survive real business conditions.

More practical resources:
https://jobaqui.com
https://hialeahoficios.com
https://miamioficios.com

#PowerBI
#DAX
#CalculatedTables
#DataModeling
#BusinessIntelligence
#DataAnalytics
#LearningByDoing

Power BI Quick Practice #04

🔵 VAR + RETURN

Power BI Quick Practice #04: VAR + RETURN

A DAX measure can be correct and still be hard to read.

That is where VAR helps.

Business problem:
You need to calculate Sales Per Customer.

Without VAR:

SalesPerCustomer =
DIVIDE(
    SUMX(Sales, Sales[Quantity] * Sales[Net Price]),
    DISTINCTCOUNT(Sales[CustomerKey])
)

This works.

But as the logic grows, it becomes harder to understand.

With VAR:

SalesPerCustomer =
VAR SalesAmount =
    SUMX(
        Sales,
        Sales[Quantity] * Sales[Net Price]
    )
VAR NumCustomer =
    DISTINCTCOUNT(Sales[CustomerKey])
RETURN
    DIVIDE(SalesAmount, NumCustomer)

What changed?

Instead of putting everything inside one formula, we named the steps:

SalesAmount = total sales calculation
NumCustomer = unique customer count

Then we used those variables in the final result.

Why this matters:
When a measure fails, you can debug each part.
When another analyst reads it, they understand the logic faster.
When the business asks for a change, the formula is easier to modify.

Use VAR when:
• the measure is getting long
• the same expression is reused
• the logic has multiple steps
• you want better readability
• you need easier debugging

Common mistake:
Writing one giant DAX formula that nobody wants to touch later.

Key idea:
A good measure should not only calculate.
It should communicate.

More practical resources:
https://jobaqui.com
https://hialeahoficios.com
https://miamioficios.com

#PowerBI
#DAX
#CalculatedTables
#DataModeling
#BusinessIntelligence
#DataAnalytics
#LearningByDoing

Power BI Quick Practice #05

🔵 SUMX()

Power BI Quick Practice #05: SUMX()

SUM() and SUMX() are not the same thing.

Business problem:
You need total sales.

But your table does not have a SalesAmount column.

Sample data:

Product | Quantity | Net Price
A       | 2        | 50
B       | 1        | 80
C       | 3        | 40
D       | 1        | 100

You cannot simply use:

SUM(Sales[SalesAmount])

because SalesAmount does not exist.

You need to calculate each row:

A: 2 × 50  = 100
B: 1 × 80  = 80
C: 3 × 40  = 120
D: 1 × 100 = 100

Then add the results:

Total Sales = 400

DAX:

Total Sales =
SUMX(
    Sales,
    Sales[Quantity] * Sales[Net Price]
)

What SUMX() does:
It goes row by row.
It evaluates the expression.
Then it adds the results.

Use SUMX() when:
• revenue = quantity × price
• margin is calculated row by row
• weighted values are needed
• the value does not exist as a physical column
• each row needs its own calculation before aggregation

Common mistake:
Using SUM() when the business calculation requires row-level evaluation.

Simple rule:
If the value already exists in one column, SUM() may be enough.
If the value must be calculated row by row, think SUMX().

More practical resources:
https://jobaqui.com
https://hialeahoficios.com
https://miamioficios.com

#PowerBI
#DAX
#CalculatedTables
#DataModeling
#BusinessIntelligence
#DataAnalytics
#LearningByDoing

Power BI Quick Practice #06

🔵 DISTINCTCOUNT()

Power BI Quick Practice #06: DISTINCTCOUNT()

Counting rows is not the same as counting unique entities.

Business problem:
You need to know how many unique customers bought something.

Sample data:

OrderID | CustomerKey | Product
1001    | C001        | A
1002    | C002        | B
1003    | C001        | C
1004    | C003        | A
1005    | C002        | D

If you count rows:

COUNTROWS(Sales) = 5

But that is the number of transactions.

The unique customers are:

C001
C002
C003

Expected result:

Unique Customers = 3

DAX:

Total Customers =
DISTINCTCOUNT(Sales[CustomerKey])

Why it matters:
Many business questions are about unique entities, not rows.

Use DISTINCTCOUNT() for:
• unique customers
• unique employees
• unique students
• unique products sold
• unique vendors
• unique cases
• unique service requests

Common mistake:
Using COUNTROWS() when the business question is asking for unique people, products, or events.

The formula must match the question.

Bad question:
How many rows do we have?

Better question:
How many unique customers did we serve?

Key idea:
Rows are not always entities.

More practical resources:
https://jobaqui.com
https://hialeahoficios.com
https://miamioficios.com

#PowerBI
#DAX
#CalculatedTables
#DataModeling
#BusinessIntelligence
#DataAnalytics
#LearningByDoing

Power BI Quick Practice #07

🟣 USERELATIONSHIP()

Power BI Quick Practice #07: USERELATIONSHIP()

Sometimes one date table needs to support more than one business date.

Business problem:
Your Sales table has two dates:

Order Date
Delivery Date

Sample data:

OrderID | Order Date | Delivery Date | SalesAmount
1001    | Jan 1      | Jan 5         | 500
1002    | Jan 3      | Jan 7         | 300
1003    | Jan 5      | Jan 10        | 700

Your Date table can only have one active relationship with Sales at a time.

Usually, the active relationship is:

Date[Date] → Sales[Order Date]

But what if the report needs sales by Delivery Date?

That is where USERELATIONSHIP() helps.

DAX:

Total Sales by Delivery Date =
CALCULATE(
    [Total Sales],
    USERELATIONSHIP(
        Sales[Delivery Date],
        'Date'[Date]
    )
)

What it does:
It temporarily activates the inactive relationship inside the measure.

Use cases:
• order date vs delivery date
• invoice date vs payment date
• hire date vs termination date
• request date vs completed date
• open date vs closed date

Common mistake:
Creating duplicate date tables without understanding why the model has multiple date roles.

Important note:
USERELATIONSHIP() does not permanently change your model.
It changes the relationship used by that specific measure.

Key idea:
One model can answer different date questions when the relationships are handled correctly.

More practical resources:
https://jobaqui.com
https://hialeahoficios.com
https://miamioficios.com

#PowerBI
#DAX
#CalculatedTables
#DataModeling
#BusinessIntelligence
#DataAnalytics
#LearningByDoing

Power BI Quick Practice #08

🟣 RELATED()

Power BI Quick Practice #08: RELATED()

RELATED() is useful when you need to bring information from a lookup table into a related fact table.

Business problem:
You have a Sales table with TerritoryKey, but you want the Territory Region.

Sales table:

OrderID | TerritoryKey | SalesAmount
1001    | 1            | 500
1002    | 2            | 300
1003    | 1            | 700

Sales Territory table:

TerritoryKey | Sales Territory Region
1            | Southeast
2            | Northwest

Expected result in Sales table:

OrderID | TerritoryKey | SalesAmount | Territory Region
1001    | 1            | 500         | Southeast
1002    | 2            | 300         | Northwest
1003    | 1            | 700         | Southeast

DAX calculated column:

Territory Region =
RELATED('Sales Territory'[Sales Territory Region])

What it does:
RELATED() follows an existing relationship and returns a value from the related table.

Use it when:
• you are in the many-side table
• you need a value from the one-side table
• a relationship already exists
• you are creating a calculated column

Useful examples:
• bring customer region into sales
• bring product category into transactions
• bring employee department into payroll
• bring territory name into sales records

Common mistake:
Using LOOKUPVALUE() when a proper relationship already exists.

Simple rule:
If the relationship exists and the model is correct, try RELATED() first.

Key idea:
Good relationships make DAX simpler.

More practical resources:
https://jobaqui.com
https://hialeahoficios.com
https://miamioficios.com

#PowerBI
#DAX
#CalculatedTables
#DataModeling
#BusinessIntelligence
#DataAnalytics
#LearningByDoing

Power BI Quick Practice #09

🟣 COUNTROWS + RELATEDTABLE()

Power BI Quick Practice #09: COUNTROWS + RELATEDTABLE()

Sometimes the business question is not:
What is the customer region?

Sometimes the question is:
How many related records does this customer have?

Business problem:
You need to count how many sales transactions belong to each customer.

Customers table:

CustomerKey | CustomerName
C001        | Ana
C002        | Luis
C003        | Maria

Sales table:

OrderID | CustomerKey | SalesAmount
1001    | C001        | 500
1002    | C002        | 300
1003    | C001        | 700
1004    | C003        | 200
1005    | C001        | 100

Expected result in Customers table:

CustomerKey | CustomerName | Total Transactions
C001        | Ana          | 3
C002        | Luis         | 1
C003        | Maria        | 1

DAX calculated column:

Total Transactions =
COUNTROWS(
    RELATEDTABLE(Sales)
)

What it does:
RELATEDTABLE(Sales) returns the sales rows related to the current customer.
COUNTROWS() counts those rows.

Use this pattern for:
• transactions per customer
• orders per vendor
• claims per policy
• tickets per employee
• enrollments per student
• inspections per location

Common mistake:
Trying to count related records without a proper relationship in the model.

Important note:
This pattern depends on model relationships.
If the relationship is wrong, the result will be wrong.

Key idea:
The model must be right before the formula becomes simple.

More practical resources:
https://jobaqui.com
https://hialeahoficios.com
https://miamioficios.com

#PowerBI
#DAX
#CalculatedTables
#DataModeling
#BusinessIntelligence
#DataAnalytics
#LearningByDoing

Power BI Quick Practice #10

🟠 CROSSJOIN()

Power BI Quick Practice #10: CROSSJOIN()

CROSSJOIN() creates all possible combinations between two tables or columns.

Business problem:
You need a planning table with every Product and every Month.

Products table:

Product ID
A
B
C

Months table:

MonthName
Jan
Feb
Mar

Expected result:

MonthName | Product ID
Jan       | A
Jan       | B
Jan       | C
Feb       | A
Feb       | B
Feb       | C
Mar       | A
Mar       | B
Mar       | C

DAX calculated table:

Planning Table =
CROSSJOIN(
    VALUES(Dates[MonthName]),
    VALUES(Products[Product ID])
)

What it does:
It combines every value from the first table with every value from the second table.

In this example:

3 months × 3 products = 9 rows

Use cases:
• budget templates
• product-month planning
• scenario tables
• forecasting structures
• missing combination analysis
• target allocation tables

Common mistake:
Forgetting that CROSSJOIN() can create many rows very quickly.

Example:

12 months × 1,000 products = 12,000 rows

That may be fine.

But:

365 dates × 100,000 products = 36,500,000 rows

That can become a serious model problem.

Key idea:
CROSSJOIN() is powerful, but it must be used intentionally.

Before using it, always ask:
How many rows will this create?

More practical resources:
https://jobaqui.com
https://hialeahoficios.com
https://miamioficios.com

#PowerBI
#DAX
#CalculatedTables
#DataModeling
#BusinessIntelligence
#DataAnalytics
#LearningByDoing