DAX QUERIES

 

🧮 DAX QUERIES – COMPLETE INTERVIEW GUIDE (AAS / Power BI)

Applies to Azure Analysis Services and Power BI

---

1️⃣ DAX BASICS (They expect this instantly)

✅ Total Sales

Total Sales =
SUM ( FactSales[SalesAmount] )

🗣 Say:

“This is a simple aggregation evaluated in filter context.”

---

✅ Total Orders

Total Orders =
COUNT ( FactSales[OrderID] )

---

2️⃣ CALCULATE – MOST IMPORTANT DAX FUNCTION 🔥

✅ Sales for Current Year

Sales CY =
CALCULATE (
    [Total Sales],
    DimDate[Year] = YEAR ( TODAY() )
)

🗣 Senior explanation:

“CALCULATE modifies the filter context before evaluating the measure.”

---

✅ Sales for a Specific Region

Sales US =
CALCULATE (
    [Total Sales],
    DimRegion[Country] = "USA"
)

---

3️⃣ TIME INTELLIGENCE (GUARANTEED QUESTIONS)

✅ Year-to-Date (YTD)

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

---

✅ Month-to-Date (MTD)

Sales MTD =
TOTALMTD (
    [Total Sales],
    DimDate[Date]
)

---

✅ Previous Year Sales

Sales PY =
CALCULATE (
    [Total Sales],
    SAMEPERIODLASTYEAR ( DimDate[Date] )
)

---

✅ YoY Growth %

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

🗣 Say:

“Time intelligence requires a proper date dimension marked as Date Table.”

---

4️⃣ FILTER vs CALCULATE (VERY COMMON)

✅ Sales for High-Value Orders

High Value Sales =
CALCULATE (
    [Total Sales],
    FILTER (
        FactSales,
        FactSales[SalesAmount] > 1000
    )
)

🗣 Senior line:

“FILTER is row context; CALCULATE converts it to filter context.”

---

5️⃣ RANKING & TOP-N (VERY POPULAR)

✅ Rank Products by Sales

Product Rank =
RANKX (
    ALL ( DimProduct[ProductName] ),
    [Total Sales],
    ,
    DESC
)

---

✅ Top 5 Products Sales

Top 5 Product Sales =
CALCULATE (
    [Total Sales],
    TOPN (
        5,
        ALL ( DimProduct ),
        [Total Sales],
        DESC
    )
)

🗣 Say:

“ALL removes existing filters before ranking.”

---

6️⃣ CONTEXT FUNCTIONS (SENIOR-LEVEL)

✅ % of Total Sales

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

---

✅ Running Total

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

---

7️⃣ CALCULATED COLUMN vs MEASURE (THEY WILL ASK)

❌ Calculated Column (Avoid unless needed)

Order Size =
IF ( FactSales[SalesAmount] > 1000, "High", "Low" )

✅ Measure (Preferred)

High Value Orders =
CALCULATE (
    COUNT ( FactSales[OrderID] ),
    FactSales[SalesAmount] > 1000
)

🗣 Strong answer:

“Measures are evaluated at query time and scale better.”

---

8️⃣ ROW-LEVEL SECURITY (RLS) 🔐

✅ Region-Based RLS

DimRegion[Region] = USERPRINCIPALNAME()

🗣 Say:

“Security is enforced at the semantic layer and inherited by Power BI.”

---

9️⃣ COMMON DAX INTERVIEW TRAPS ❌

Mistake	Correct Thinking
Confusing row & filter context	Always clarify context
Using calculated columns for aggregations	Use measures
No date table	Time intelligence fails
Overusing ALL	Breaks slicers

---

🔟 MOST-ASKED DAX INTERVIEW QUESTIONS (WITH ANSWERS)

❓ Difference between ALL and ALLEXCEPT?

ALL removes all filters; ALLEXCEPT keeps selected columns filtered.

---

❓ What is context transition?

CALCULATE converts row context into filter context.

---

❓ When to use SUMX instead of SUM?

When calculation depends on row-by-row logic.

Total Discount =
SUMX (
    FactSales,
    FactSales[Quantity] * FactSales[Discount]
)

---

1️⃣1️⃣ PERFORMANCE BEST PRACTICES (SENIOR SIGNAL 🚀)

✔ Prefer measures
✔ Reduce cardinality
✔ Avoid bi-directional filters
✔ Use star schema
✔ Pre-aggregate when possible

🗣 Say:

“Good model design improves DAX performance more than complex formulas.”

---

1️⃣2️⃣ PERFECT INTERVIEW ANSWER (READY TO USE)

“We built tabular models in Azure Analysis Services using DAX measures for KPIs like YTD, YoY, and rankings. CALCULATE and time-intelligence functions were heavily used, with RLS enforced centrally. Models were optimized using star schema and minimal calculated columns.”

---

🔥 10-MIN RAPID PRACTICE (Before Interview)

Revise these only:

• CALCULATE

• FILTER

• ALL

• RANKX

• TOTALYTD

• SAMEPERIODLASTYEAR