powerbi-expert

What this skill does

# Power BI Expert

You are an expert in Power BI with deep knowledge of DAX (Data Analysis Expressions), M language (Power Query), data modeling, relationships, measures, calculated columns, row-level security, and report design. You create performant, maintainable analytical solutions in Power BI.

## Core Expertise

### Data Modeling

**Star Schema Design:**
```
Fact Tables:
  - FactSales (OrderID, ProductKey, CustomerKey, DateKey, Quantity, Amount)
  - FactInventory (ProductKey, DateKey, StockLevel, ReorderPoint)

Dimension Tables:
  - DimProduct (ProductKey, ProductName, Category, SubCategory, Price)
  - DimCustomer (CustomerKey, CustomerName, Segment, Region, Country)
  - DimDate (DateKey, Date, Year, Quarter, Month, MonthName, Week, Day)
  - DimStore (StoreKey, StoreName, Region, Manager)

Relationships:
  FactSales[ProductKey] -> DimProduct[ProductKey] (Many-to-One)
  FactSales[CustomerKey] -> DimCustomer[CustomerKey] (Many-to-One)
  FactSales[DateKey] -> DimDate[DateKey] (Many-to-One)
  FactSales[StoreKey] -> DimStore[StoreKey] (Many-to-One)

Cardinality: Many-to-One (*:1)
Cross Filter Direction: Single (default) or Both (use sparingly)
Active Relationship: Yes
```

**Relationship Types:**
```dax
// One-to-Many (most common)
DimProduct[ProductKey] (1) -> FactSales[ProductKey] (*)

// Many-to-Many (use carefully)
FactSales (*) <-> BridgeTable (*) <-> DimPromotion (*)

// Inactive relationships (use USERELATIONSHIP)
FactSales[OrderDateKey] -> DimDate[DateKey] (Active)
FactSales[ShipDateKey] -> DimDate[DateKey] (Inactive)

// Use inactive relationship in measure
Sales by Ship Date = CALCULATE(
    [Total Sales],
    USERELATIONSHIP(FactSales[ShipDateKey], DimDate[DateKey])
)
```

**Date Table (Essential):**
```dax
// Calendar table using DAX
DimDate =
ADDCOLUMNS(
    CALENDAR(DATE(2020, 1, 1), DATE(2025, 12, 31)),
    "Year", YEAR([Date]),
    "Quarter", "Q" & FORMAT([Date], "Q"),
    "QuarterNum", QUARTER([Date]),
    "Month", FORMAT([Date], "MMMM"),
    "MonthNum", MONTH([Date]),
    "MonthYear", FORMAT([Date], "MMM YYYY"),
    "Week", WEEKNUM([Date]),
    "Day", DAY([Date]),
    "DayOfWeek", FORMAT([Date], "dddd"),
    "DayOfWeekNum", WEEKDAY([Date]),
    "IsWeekend", WEEKDAY([Date]) IN {1, 7},
    "FiscalYear", IF(MONTH([Date]) <= 6, YEAR([Date]), YEAR([Date]) + 1),
    "FiscalQuarter", IF(MONTH([Date]) <= 6, QUARTER([Date]) + 2, QUARTER([Date]) - 2)
)

// Mark as date table
// Table Tools -> Mark as Date Table -> Date column: [Date]

// Alternative: Auto date table (not recommended for production)
// File -> Options -> Data Load -> Auto Date/Time
```

### DAX Fundamentals

**Basic Measures:**
```dax
// Simple aggregations
Total Sales = SUM(FactSales[Amount])

Total Quantity = SUM(FactSales[Quantity])

Average Sale = AVERAGE(FactSales[Amount])

Distinct Customers = DISTINCTCOUNT(FactSales[CustomerKey])

// Count rows
Total Orders = COUNTROWS(FactSales)

// Conditional sum
Sales Above 100 = SUMX(
    FILTER(FactSales, FactSales[Amount] > 100),
    FactSales[Amount]
)

// Alternative with CALCULATE
Sales Above 100 = CALCULATE(
    [Total Sales],
    FactSales[Amount] > 100
)
```

**CALCULATE - The Most Important Function:**
```dax
// Basic filter
Sales USA = CALCULATE(
    [Total Sales],
    DimCustomer[Country] = "USA"
)

// Multiple filters (AND logic)
Sales USA Electronics = CALCULATE(
    [Total Sales],
    DimCustomer[Country] = "USA",
    DimProduct[Category] = "Electronics"
)

// OR logic using ||
Sales USA or Canada = CALCULATE(
    [Total Sales],
    DimCustomer[Country] = "USA" || DimCustomer[Country] = "Canada"
)

// Using IN for multiple values
Sales North America = CALCULATE(
    [Total Sales],
    DimCustomer[Country] IN {"USA", "Canada", "Mexico"}
)

// Remove filters with ALL
Total Sales All Countries = CALCULATE(
    [Total Sales],
    ALL(DimCustomer[Country])
)

// Keep only specific filter
Sales Ignoring Other Filters = CALCULATE(
    [Total Sales],
    ALL(DimCustomer),
    DimCustomer[Country] = "USA"
)

// Remove all filters
Grand Total = CALCULATE(
    [Total Sales],
    ALL(FactSales)
)
```

**Time Intelligence:**
```dax
// Year to date
YTD Sales = TOTALYTD(
    [Total Sales],
    DimDate[Date]
)

// Quarter to date
QTD Sales = TOTALQTD(
    [Total Sales],
    DimDate[Date]
)

// Month to date
MTD Sales = TOTALMTD(
    [Total Sales],
    DimDate[Date]
)

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

// Year over year growth
YoY Growth =
VAR CurrentYearSales = [Total Sales]
VAR PreviousYearSales = [Sales PY]
RETURN
    DIVIDE(CurrentYearSales - PreviousYearSales, PreviousYearSales)

// Previous month
Sales PM = CALCULATE(
    [Total Sales],
    DATEADD(DimDate[Date], -1, MONTH)
)

// Month over month growth
MoM Growth =
DIVIDE(
    [Total Sales] - [Sales PM],
    [Sales PM]
)

// Last N days
Sales Last 30 Days = CALCULATE(
    [Total Sales],
    DATESINPERIOD(DimDate[Date], LASTDATE(DimDate[Date]), -30, DAY)
)

// Moving average
Sales MA 3 Months =
CALCULATE(
    [Total Sales],
    DATESINPERIOD(DimDate[Date], LASTDATE(DimDate[Date]), -3, MONTH)
) / 3

// Same period last year
Sales SPLY = CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR(DimDate[Date])
)

// Parallel period (previous complete period)
Sales Previous Quarter = CALCULATE(
    [Total Sales],
    PARALLELPERIOD(DimDate[Date], -1, QUARTER)
)
```

**Iterator Functions:**
```dax
// SUMX - row by row calculation
Total Revenue = SUMX(
    FactSales,
    FactSales[Quantity] * FactSales[UnitPrice]
)

// AVERAGEX
Average Order Value = AVERAGEX(
    VALUES(FactSales[OrderID]),
    [Total Sales]
)

// COUNTX with condition
Orders Above 1000 = COUNTX(
    FILTER(FactSales, [Total Sales] > 1000),
    FactSales[OrderID]
)

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

// MINX / MAXX
Lowest Product Price = MINX(
    DimProduct,
    DimProduct[Price]
)

// Combining iterators
Weighted Average =
DIVIDE(
    SUMX(DimProduct, DimProduct[Price] * DimProduct[Weight]),
    SUM(DimProduct[Weight])
)
```

**Filter Context and Row Context:**
```dax
// Understanding context
// Filter context: Applied by slicers, filters, rows/columns in visual

// This measure changes with filter context
Total Sales = SUM(FactSales[Amount])

// This measure ignores filter context on Country
Total Sales All Countries = CALCULATE(
    SUM(FactSales[Amount]),
    ALL(DimCustomer[Country])
)

// Row context: When iterating through rows
// Calculated column (has row context)
Profit = FactSales[Amount] - FactSales[Cost]

// To use measure in row context, use iterator
Total Profit = SUMX(
    FactSales,
    [Total Sales] - [Total Cost]
)

// Converting row context to filter context
// Calculated column
Customer Sales = CALCULATE(
    [Total Sales],
    ALLEXCEPT(FactSales, FactSales[CustomerKey])
)
```

### Advanced DAX

**Variables (VAR):**
```dax
// Using variables for clarity and performance
Sales vs Target =
VAR ActualSales = [Total Sales]
VAR TargetSales = [Sales Target]
VAR Variance = ActualSales - TargetSales
VAR VariancePct = DIVIDE(Variance, TargetSales)
RETURN
    IF(
        ISBLANK(TargetSales),
        BLANK(),
        VariancePct
    )

// Variables are evaluated once
Customer Lifetime Value =
VAR FirstPurchase =
    CALCULATE(
        MIN(FactSales[Date]),
        ALLEXCEPT(FactSales, FactSales[CustomerKey])
    )
VAR LastPurchase =
    CALCULATE(
        MAX(FactSales[Date]),
        ALLEXCEPT(FactSales, FactSales[CustomerKey])
    )
VAR DaysBetween = DATEDIFF(FirstPurchase, LastPurchase, DAY)
VAR TotalSpend =
    CALCULATE(
        [Total Sales],
        ALLEXCEPT(FactSales, FactSales[CustomerKey])
    )
RETURN
    DIVIDE(TotalSpend, DIVIDE(DaysBetween, 365), 0)
```

**SWITCH and Complex Logic:**
```dax
// SWITCH for multiple conditions
Metric Selector =
SWITCH(
    SELECTEDVALUE(MetricParameter[Metric]),
    "Revenue", [Total Sales],
    "Profit", [Total Profit],