Skip to content

Instantly share code, notes, and snippets.

@rajrao

rajrao/calendar_instructions_and_examples.md

Created February 8, 2026 22:43
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save rajrao/e1caac9c2b2685ea4aa3b0a063ca93a6 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save rajrao/e1caac9c2b2685ea4aa3b0a063ca93a6 to your computer and use it in GitHub Desktop.
Download ZIP
From powerbi-modeling-mcp.exe (0.19.1) calendar_instructions_and_examples.md
Raw
calendar_instructions_and_examples.md
name description uriTemplate
Calendar Instructions and Examples
Guidelines for creating Power BI calendar objects
resource://calendar_instructions_and_examples

Calendar Column Groups Guide

This guide explains how to define calendar column groups in a Power BI date table so time intelligence works as expected and consistently across models.

Concepts

  • Calendar Column Groups. Use these when a primary column cleanly represents a standard time unit such as Year, Quarter, Month, Week, or Date. Time units (including "of year" variants) are defined by a fixed enumeration; use the exact casing shown below.
  • Time-related groups. Use these for relative columns that are time-aware but are not a standard time unit (for example, RelativeMonth with values like "Current"/"Previous"). They can be used to slice/label time-aware analyses but do not themselves define a standard unit.
  • Primary vs. associated columns. When a column maps to a specific unit, make it the primaryColumn for that unit. If column A is sorted by column B (Power BI SortByColumn), then B should be the primaryColumn and A should be an associatedColumn. Optionally add other 1-to-1 associatedColumns for alternate labels (e.g., a long and a short month name).

Mapping guidance

  • Prefer a time unit group when the mapping is unambiguous; use the exact unit name from the list below.
  • Use a time-related group for relative states (e.g., current/previous/next) that don't represent a standard unit. Time-related groups have unknown time units.
  • For textual labels that are sorted by another column, use the sort column as the primary and add the text label as an associatedColumn.
  • Add associatedColumns only for strict 1-to-1 label relationships.
  • Each calendar definition should use columns from only its host table.
  • Build hierarchies that roll up cleanly (Year → Quarter → Month → Date).
  • Do not repeat a time unit within the same calendar.
  • Columns tagged in a calendar must be tagged to the same time unit (or as a time-related column) across all calendars.
  • Do not use the same physical column more than once in the same calendar.
  • Associated columns are optional but must be 1-to-1 with the primary.
  • Complete vs. Partial units:
    • Complete units uniquely identify a single period and must include the calendar context (e.g., include the year): Year, Quarter, Month, Week, Date.
      • Examples: 2024 (Year), Q3 2024 (Quarter), 2024-01 or "January 2024" (Month), 2024-W49 (Week), 2024-01-15 (Date).
    • Partial units are positions within a larger period and are not unique by themselves: QuarterOfYear (1–4), MonthOfYear (1–12 or names), WeekOfYear (1–52/53), DayOfYear (1–365/366). Variants exist for Quarter/Month (e.g., MonthOfQuarter).
      • Use these primarily for labels, slicers, or seasonality—not as keys or for hierarchical rollups.
    • Mapping examples:
      • "December 2024" → Month (complete, includes year). "December" → MonthOfYear (not unique across years).
      • "Q3 2023" → Quarter. "Q3" → QuarterOfYear.
      • "2024-W49" or "Week 49 of 2024" → Week. "Week 49" → WeekOfYear.
      • "15th day of month" → DayOfMonth. "15th day of the year" → DayOfYear.
    • Rules of thumb:
      • For standard hierarchies (Year → Quarter → Month → Date), use complete units at every level.
      • You may associate a partial label with a complete primary (e.g., Month primary: Year Month; associated label: MonthOfYear name) if it is 1-to-1 with the primary.
      • Do not map MonthOfYear to Month, WeekOfYear to Week, or QuarterOfYear to Quarter—these are different concepts.
      • For weeks, prefer ISO Year-Week for complete Week labels. If your organization uses a non-ISO week system, still include the year context and use your defined week-numbering convention.

Allowed time units

timeUnits:
  - id: Unknown
    example: "IsWeekend"
    note: "Used for both season-type and period-type time-related columns. Future enhancements may provide separate categories."
  - id: Year
    example: 2022
  - id: Quarter
    example: "Q3 2022"
  - id: QuarterOfYear
    example: 4        # 4th quarter of the year
  - id: Month
    example: "January 2022"
  - id: MonthOfYear
    example: "January"
  - id: MonthOfQuarter
    example: 2        # 2nd month of the quarter
  - id: Week
    example: "2022-W49"   # ISO Year-Week or unique year+week label
  - id: WeekOfYear
    example: 49
  - id: WeekOfQuarter
    example: 11
  - id: WeekOfMonth
    example: 3
  - id: Date
    example: "2022-01-01"
  - id: DayOfYear
    example: 241
  - id: DayOfQuarter
    example: 71
  - id: DayOfMonth
    example: 23
  - id: DayOfWeek
    example: 4         # e.g., Thursday if 1=Monday

Example

Tables:
  - Name: DimDate
    Columns:
      - Name: Date
        Type: Date
      - Name: Year
        Type: Integer
      - Name: Quarter
        Type: Text
        SortByColumnName: Year Quarter Number
      - Name: Year Quarter
        Type: Text
        SortByColumnName: Year Quarter Number
      - Name: Year Quarter Number
        Type: Integer
      - Name: Month
        Type: Text
        SortByColumnName: Month Number
      - Name: Month Short
        Type: Text
        SortByColumnName: Month Number
      - Name: Month Number
        Type: Integer
      - Name: Year Month
        Type: Text
        SortByColumnName: Year Month Number
      - Name: Year Month Short
        Type: Text
        SortByColumnName: Year Month Number
      - Name: Year Month Number
        Type: Integer
      - Name: Week of Year
        Type: Integer
      - Name: ISO Year-Week
        Type: Text
        SortByColumnName: ISO Year-Week Number
      - Name: ISO Year-Week Number
        Type: Integer
      - Name: Fiscal Year Number
        Type: Integer
      - Name: Fiscal Year Name
        Type: Text
        SortByColumnName: Fiscal Year Number
      - Name: Fiscal Year Month
        Type: Text
        SortByColumnName: Fiscal Year Month Number
      - Name: Fiscal Year Month Number
        Type: Integer
      - Name: Fiscal Month Number of Year
        Type: Integer
      - Name: Fiscal Month Name
        Type: Text
        SortByColumnName: Fiscal Month Number of Year
      - Name: RelativeMonth  # Period-type: represents relative states
        Type: Text
      - Name: Season         # Season-type: represents cyclical concepts
        Type: Text
    Calendars:
      - name: Gregorian Calendar
        calendarColumnGroups:
          - timeUnit: Year
            primaryColumn: Year
          - timeUnit: Quarter
            primaryColumn: Year Quarter Number
            associatedColumns:
              - Year Quarter
          - timeUnit: Month
            primaryColumn: Year Month Number
            associatedColumns:
              - Year Month
              - Year Month Short
          - timeUnit: Week
            primaryColumn: ISO Year-Week Number
            associatedColumns:
              - ISO Year-Week
          - timeUnit: WeekOfYear
            primaryColumn: Week of Year
          - timeUnit: Date
            primaryColumn: Date
      - name: Fiscal Calendar
        calendarColumnGroups:
          - timeUnit: Year
            primaryColumn: Fiscal Year Number
            associatedColumns:
              - Fiscal Year Name
          - timeUnit: Month
            primaryColumn: Fiscal Year Month Number
            associatedColumns:
              - Fiscal Year Month
          - timeUnit: MonthOfYear
            primaryColumn: Fiscal Month Number of Year
            associatedColumns:
              - Fiscal Month Name
        timeRelatedGroups:
          - column: RelativeMonth
          - column: Season
Raw
dax_query_instructions_and_examples.md
name description uriTemplate
DAX Query Instructions and Examples
Guidelines for writing Power BI DAX queries
resource://dax_query_instructions_and_examples

DAX Query Language Guide

Overview

DAX (Data Analysis Expressions) is a formula language used in Power BI for creating custom calculations and queries. This guide provides comprehensive instructions and examples for writing valid DAX query expressions.

DAX Query Best Practices

When writing DAX queries, follow these recommendations for optimal results:

  • Include comments for clarity (DAX comments use // not --)
  • Always include an ORDER BY clause when returning multiple rows
  • Use meaningful variable names to improve readability
  • Define measures with fully qualified names in DEFINE blocks

DAX Query Syntax Rules

Query Structure

DEFINE Block

  • Use DEFINE at the beginning if the query includes VAR, MEASURE, COLUMN, or TABLE definitions
  • Only use a single DEFINE block per query
  • Separate definitions with new lines (no commas or semicolons)

Measure Definitions

  • When defining: ALWAYS fully qualify the measure name including its host table
    • Example: DEFINE MEASURE 'TableName'[MeasureName] = ...
    • The host table must exist in the semantic model
  • When using: Refer to the measure by name only, without the table qualifier
    • Example: Use [MeasureName] in expressions like CALCULATE([MeasureName], ...)

Ordering Results

  • ALWAYS include an ORDER BY clause when EVALUATE returns multiple rows
  • Do not use the ORDERBY function to sort the final query result

CALCULATE and CALCULATETABLE Filter Rules

Boolean filters in CALCULATE or CALCULATETABLE have important restrictions:

  • Cannot directly use a measure or another CALCULATE function
    • Solution: Use a variable to store the result, then reference the variable
  • Cannot reference columns from two different tables
  • When using the IN operator, the table operand must be a table variable, not a table expression
  • Do not assign a boolean filter to a VAR definition

SUMMARIZECOLUMNS Function

Purpose: Build summary tables with groupby columns and measure-like extension columns

Parameter Order (all optional, but must follow this order if used):

  1. Groupby columns (can be from one or multiple tables)
  2. Filters
  3. Measures or measure-like calculations

Key Rules:

  • Use SUMMARIZECOLUMNS as the default for building summary tables with measures
  • Do not use SUMMARIZECOLUMNS without measure-like extension columns
  • Returns only rows where at least one measure value is not BLANK
  • Allows ANY number of measure-like calculations of arbitrary complexity
  • DO NOT use boolean filters with SUMMARIZECOLUMNS

When to Use Alternatives:

  • If there are no measures or calculations, use SUMMARIZE instead

SUMMARIZE Function

Allowed Pattern:

SUMMARIZE(<table expression>, <column1>, …, <columnN>)

Critical Restrictions:

  • NEVER use SUMMARIZE with measure-like expressions
    • ❌ Incorrect: SUMMARIZE(<table>, <column>, "expr1", <expr1>, …)
    • ✅ Correct: Use SUMMARIZECOLUMNS for measure calculations
  • Use for extracting distinct combinations of columns only
  • VALUES('Table'[Column]) is a shortcut for SUMMARIZE('Table', 'Table'[Column])
  • When extracting a column from a table variable: SUMMARIZE(_TableVar, [Column])
    • Note: _TableVar[Column] is not valid syntax

When to Use Alternatives:

  • For measure calculations: Use SUMMARIZECOLUMNS
  • For aggregations on table variables: Use GROUPBY

GROUPBY Function

Purpose: Perform simple aggregations on table-valued variables at a grouped level

Key Rules:

  • Only use GROUPBY with a table-valued variable as the first argument
  • The CURRENTGROUP function is valid ONLY within GROUPBY
  • CURRENTGROUP must not be used elsewhere

SELECTCOLUMNS Function

Purpose: Project columns while preserving duplicates or renaming columns

Key Rules:

  • Use to preserve duplicate rows (unlike SUMMARIZE which removes them)
  • Use to rename columns for clarity
  • When renaming columns, subsequent expressions (TOPN, ORDER BY) must use the NEW column names

Important: Include all columns needed for later operations (ORDER BY, FILTER, etc.)

Table Expressions and Filters

  • When using table expressions (SELECTCOLUMNS, CALCULATETABLE), include any columns needed later
  • Filters applied to one table can propagate across relationships based on filter direction (unidirectional or bidirectional)

Set Functions

When using INTERSECT, UNION, or EXCEPT:

  • Both input tables must produce an identical number of columns

Time Intelligence Functions

DATESINPERIOD Rolling Windows:

  • The negative period offset must precisely match the number of periods required
  • Examples:
    • 12-month window: Use -12 (not -11)
    • 3-month window: Use -3 (not -2)
  • This prevents off-by-one errors

Maintaining Clear Date Context:

  • Always establish a valid date context for time intelligence calculations
  • Methods:
    • Include groupby columns from the date table, OR
    • Apply filters on date columns
  • Without date context, time intelligence functions cannot determine a "current date" reference
  • When using ROW function with time intelligence, supply external filters through CALCULATETABLE

Sample Data Model

These examples use a simplified hypothetical data model:

Tables:
  - Name: Sales
    Measures:
      - Name: Total Discount
        Type: Decimal
      - Name: Total Amount
        Type: Decimal
      - Name: Total Quantity
        Type: Integer
    Columns:
      - Name: CustomerKey
        Type: Text
      - Name: Order Quantity
        Type: Integer
      - Name: ProductKey
        Type: Text
      - Name: OrderDate
        Type: Date
      - Name: Sales Amount
        Type: Decimal
  - Name: Product
    Measures:
      - Name: Median List Price
        Type: Decimal
    Columns:
      - Name: Category
        Type: Text
        MinValue: Consumer Electronics
        MaxValue: Toys
      - Name: Color
        Type: Text
        MinValue: Beige
        MaxValue: Red
      - Name: List Price
        Description: Retail price of the product
        Type: Decimal
      - Name: Name
        Type: Text
      - Name: ProductKey
        Type: Text
  - Name: Customer
    Columns:
      - Name: CustomerKey
        Type: Text
      - Name: Name
        Type: Text
      - Name: Age
        Type: Integer
  - Name: Calendar
    Columns:
      - Name: Date
        Type: Date
      - Name: Month
        Type: Text
        SortByColumnName: MonthNumberOfYear
      - Name: MonthNumberOfYear
        Type: Integer
      - Name: Year
        Type: Integer
Active Relationships:
  - PK: 'Product'[ProductKey]
    FK: 'Sales'[ProductKey]
    Unidirectional Filter Propagation: "'Product' filters 'Sales'"
  - PK: 'Customer'[CustomerKey]
    FK: 'Sales'[CustomerKey]
    Unidirectional Filter Propagation: "'Customer' filters 'Sales'"
  - PK: 'Calendar'[Date]
    FK: 'Sales'[OrderDate]
    Unidirectional Filter Propagation: "'Calendar' filters 'Sales'"

DAX Query Examples

The following examples demonstrate proper DAX query syntax and best practices using the data model defined above.

Example 1: Time Intelligence with Rolling Averages

Scenario: Calculate year-to-date total sales and 14-day moving average for red products.

// Year-to-date total sales and 14-day moving average of sales for red products.
EVALUATE
  CALCULATETABLE(
    ROW(
      "Total Sales Amount YTD", TOTALYTD([Total Amount], 'Calendar'[Date]),
      "Total Sales Amount 14-Day MA", AVERAGEX(DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -14, DAY), [Total Amount]) // Note that the number_of_intervals parameter must be -14 instead of -13.
    ),
    'Product'[Color] == "Red",
    TREATAS({ MAX('Sales'[OrderDate]) }, 'Calendar'[Date]) // Establish a reference date for TI functions TOTALYTD and DATESINPERIOD.
  )

Key Concepts:

  • Uses CALCULATETABLE with ROW to establish date context for time intelligence functions
  • TREATAS establishes a clear "current date" reference for time intelligence
  • DATESINPERIOD uses -14 (not -13) for a proper 14-day window

Example 2: Multi-Level Aggregation with GROUPBY

Scenario: Calculate average, minimum, and maximum monthly sales quantity by year for Consumer Electronics before 2023.

DEFINE
  // Filters for products in Consumer Electronics category
  VAR _Filter1 = TREATAS(
    {
      "Consumer Electronics"
    },
    'Product'[Category]
  )
  // Filters to years before 2023
  VAR _Filter2 = FILTER(
    ALL('Calendar'[Year]),
    'Calendar'[Year] < 2023
  )

// Quantity filtered to Consumer Electronics products for years before 2023, grouped by month
  VAR _SummaryTable = SUMMARIZECOLUMNS(
    'Calendar'[Year],
    'Calendar'[Month],
    // [Month] is a required groupby column.
    // A query always sorts by required groupby columns.
    // [MonthNumberOfYear] is the orderby column for [Month].
    // Also include [MonthNumberOfYear] to be used in the ORDER BY clause.
    'Calendar'[MonthNumberOfYear],
    _Filter1,
    _Filter2,
    "Monthly Quantity", [Total Quantity]
  )
// Aggregate the summarized monthly data by year and month to derive average, minimum, and maximum monthly quantities.
EVALUATE
  // GROUPBY function is used to summarize intermediate tables.
  GROUPBY(
    _SummaryTable,
    'Calendar'[Year],
    'Calendar'[Month],
    'Calendar'[MonthNumberOfYear],
    "Avg Monthly Quantity",
    AVERAGEX(
      CURRENTGROUP(), // must be used inside GROUPBY function
      [Monthly Quantity]
    ),
    "Min Monthly Quantity",
    MINX(
      CURRENTGROUP(), // must be used inside GROUPBY function
      [Monthly Quantity]
    ),
    "Max Monthly Quantity",
    MAXX(
      CURRENTGROUP(),  // must be used inside GROUPBY function
      [Monthly Quantity]
    )
  )
  ORDER BY
    'Calendar'[Year] ASC,
    // [MonthNumberOfYear] is the orderby column for [Month].
    // ORDER BY [MonthNumberOfYear] instead of [Month].
    'Calendar'[MonthNumberOfYear] ASC

Key Concepts:

  • SUMMARIZECOLUMNS creates initial summary with measures
  • GROUPBY performs secondary aggregation on the summary table
  • CURRENTGROUP is used exclusively within GROUPBY
  • Includes MonthNumberOfYear for proper sorting

Example 3: Filtering with Measures Using Variables

Scenario: Find products with total sales over $1 million that are red or black.

DEFINE
  // Red or Black product filter
  VAR _Filter = TREATAS(
    {
      "Red",
      "Black"
    },
    'Product'[Color]
  )
  // Sales of Red or Black products
  VAR _SummaryTable = SUMMARIZECOLUMNS(
    'Product'[Name],
    _Filter,
    "Total Sales", [Total Amount]
  )

// Products with total sales above $1,000,000
EVALUATE
  SELECTCOLUMNS(
    FILTER(
      _SummaryTable,
      [Total Sales] > 1000000
    ),
    'Product'[Name]
  )
  ORDER BY
    'Product'[Name] ASC

Key Concepts:

  • TREATAS creates a filter from a list of values
  • SUMMARIZECOLUMNS builds summary with measure
  • FILTER applied to summary table variable
  • SELECTCOLUMNS projects only needed columns

Example 4: SUMMARIZE for Distinct Values (No Duplicates)

Scenario: Get unique combinations of color, category, and product key for products sold in 2022.

// Product color, category and key for products sold in 2022
EVALUATE
  CALCULATETABLE(
    // SUMMARIZE is used to remove duplicate rows
    SUMMARIZE(
      'Sales',
      'Product'[Color],
      'Product'[Category],
      'Sales'[ProductKey]
    ),
    'Calendar'[Year] == 2022
  )
  ORDER BY
    'Product'[Color] ASC,
    'Product'[Category] ASC,
    'Sales'[ProductKey] ASC

Key Concepts:

  • SUMMARIZE removes duplicate rows
  • CALCULATETABLE applies year filter
  • Related table columns accessed via relationships

Example 5: SELECTCOLUMNS for Preserving Duplicates

Scenario: Get color, category, and product key for products sold in 2022, keeping all duplicate rows.

// Product color, category and key for products sold in 2022
EVALUATE
  CALCULATETABLE(
    SELECTCOLUMNS(
      'Sales',
      "Color",
      RELATED('Product'[Color]),
      "Category",
      RELATED('Product'[Category]),
      'Sales'[ProductKey]
    ),
    'Calendar'[Year] == 2022
  )
  ORDER BY
    [Color] ASC,
    [Category] ASC,
    'Sales'[ProductKey] ASC

Key Concepts:

  • SELECTCOLUMNS preserves duplicate rows (unlike SUMMARIZE)
  • Column renaming requires using new names in ORDER BY
  • RELATED accesses columns from related tables

Example 6: Finding Products with No Sales

Scenario: Identify products that have never been sold.

DEFINE
  // Sale row count
  MEASURE 'Sales'[Row Count] = COUNTROWS()

// Products with no sales
EVALUATE
  FILTER(
    'Product',
    ISBLANK([Row Count])
  )
  ORDER BY
    'Product'[Name] ASC,
    'Product'[ProductKey] ASC

Key Concepts:

  • Defines a measure for row counting
  • FILTER with ISBLANK identifies products with no related sales
  • Filter context propagates from Product to Sales table

Example 7: Using Variables to Store Measure Results

Scenario: Find products with list prices above the median.

Solution 1 - Using CALCULATETABLE:

DEFINE
  // Calculate the value of the [Median List Price] measure and store the result in a variable.
  VAR _MedianListPrice = [Median List Price]

// Products with list price over the median.
EVALUATE
  CALCULATETABLE(
    VALUES('Product'[Name]),
    'Product'[List Price] > _MedianListPrice // boolean filter uses variable instead of measure directly
  )
  ORDER BY
    'Product'[Name] ASC

Solution 2 - Using FILTER:

DEFINE
  // Calculate the value of the [Median List Price] measure and store the result in a variable.
  VAR _MedianListPrice = [Median List Price]

// Products with list price over the median.
EVALUATE
  SELECTCOLUMNS(
    FILTER(
      VALUES('Product'),
      'Product'[List Price] > _MedianListPrice // Use variable instead of measure reference to ensure the median list price is calculated across all products
    ),
    'Product'[Name]
  )
  ORDER BY
    'Product'[Name] ASC

Key Concepts:

  • Variables store measure results for use in boolean filters
  • Cannot use measures directly in CALCULATE boolean filters
  • Both approaches yield the same result with different syntax

Example 8: Using Table Variables as Filters

Scenario: Find the product with highest demand since 2020 and get its sale dates.

DEFINE
  // To make query more readable, a filter can be defined separately.
  VAR _Filter = FILTER(
    ALL('Calendar'[Year]),
    'Calendar'[Year] >= 2020
  )
  // Get the product with the maximum Total Quantity
  VAR _TopProduct = TOPN(
    1,
    SUMMARIZECOLUMNS(
      'Product'[ProductKey],
      _Filter,
      "Total Quantity", [Total Quantity]
    ),
    [Total Quantity],
    DESC
  )

// Name and order date for sales of the top product
EVALUATE
  SELECTCOLUMNS(
    CALCULATETABLE(
      'Sales',
      // Use table-valued variable _TopProduct directly as a filter.
      // No need to extract the 'Product'[ProductKey] first.
      // Calculated column [Total Quantity] has no effect in the filter context.
      _TopProduct
    ),
    "Product Name",
    RELATED('Product'[Name]),
    'Sales'[OrderDate]
  )
  ORDER BY
    [Product Name] ASC,
    'Sales'[OrderDate] ASC

Key Concepts:

  • TOPN identifies the product with highest total quantity
  • Table variables can be used directly as filters in CALCULATETABLE
  • Calculated columns in table variables don't affect filter context

Example 9: Calculating Averages on Filtered Subsets

Scenario: Calculate the average list price of products sold in 2022.

DEFINE
  // Distinct products sold in 2022
  VAR _ProductsSold2022 = CALCULATETABLE(
    SUMMARIZE(
      'Sales',
      'Product'[ProductKey]
    ),
    'Calendar'[Year] == 2022
  )
EVALUATE
  ROW(
    "Average List Price of Products Sold in 2022",
    CALCULATE(
      AVERAGE('Product'[List Price]),
      _ProductsSold2022 // Apply table-valued variable as a filter
    )
  )

Key Concepts:

  • SUMMARIZE extracts distinct products sold in 2022
  • ROW returns a single-row result
  • Table variable applied as filter in CALCULATE

Example 10: Column Renaming with SELECTCOLUMNS

Scenario: Get products sold and customers, sorted by renamed column names.

// Sorted product and customer names for all sales
DEFINE
  VAR _UniqueProductCustomerPairs = SUMMARIZE(
    'Sales',
    'Product'[Name],
    'Customer'[Name]
  )
EVALUATE
  SELECTCOLUMNS(
    _UniqueProductCustomerPairs,
    "Product Name", // New name for the 'Product'[Name] column
    'Product'[Name],
    "Customer Name", // New name for the 'Customer'[Name] column
    'Customer'[Name]
  )
  // ORDER BY needs to use the renamed column names
  ORDER BY
    [Product Name] ASC, // Use the new column name assigned by SELECTCOLUMNS instead of the original column name 'Product'[Name]
    [Customer Name] ASC // Use the new column name assigned by SELECTCOLUMNS instead of the original column name 'Customer'[Name]

Key Concepts:

  • SELECTCOLUMNS renames columns for clarity
  • ORDER BY must reference the NEW column names, not original names
  • SUMMARIZE removes duplicate product-customer pairs

Example 11: Multiple Filters with TREATAS

Scenario: For the three oldest customers, show total discounts by year for the last three years.

DEFINE
  VAR _OldestThreeCustomers = TOPN(
    3,
    'Customer',
    'Customer'[Age],
    DESC
  )
  // Determine the last year based on actual sales dates.
  // Avoid using the last year in the 'Calendar' table, as it may include future dates without sales.
  VAR _LastYear = YEAR(MAX('Sales'[OrderDate]))
EVALUATE
  SUMMARIZECOLUMNS(
    'Customer'[Name],
    'Calendar'[Year],
    TREATAS({_LastYear, _LastYear - 1, _LastYear - 2}, 'Calendar'[Year]),
    _OldestThreeCustomers, // Apply table-valued variable as filter.
    "Total Discount",
    [Total Discount]
  )
  ORDER BY
    'Customer'[Name] ASC,
    'Calendar'[Year] ASC

Key Concepts:

  • TOPN selects top 3 customers by age
  • TREATAS creates filter from calculated year values
  • Determines last year from actual sales data, not calendar table

Example 12: Filtering Aggregated Results

Scenario: For each customer, show products purchased at least three times with purchase count.

DEFINE
  MEASURE 'Sales'[Purchase Count] = COUNTROWS()
  VAR _SummaryTable = SUMMARIZECOLUMNS(
    'Customer'[Name],
    'Product'[Name],
    "Purchase Count", [Purchase Count]
  )
EVALUATE
  FILTER(_SummaryTable, [Purchase Count] >= 3)
  ORDER BY
    'Customer'[Name] ASC,
    'Product'[Name] ASC

Key Concepts:

  • Defines measure for counting purchases
  • SUMMARIZECOLUMNS creates summary with measure
  • FILTER applied to summary table variable
  • Simple and efficient two-step pattern

Example 13: Calculated Columns in DEFINE

Scenario: Categorize products by price (above/below median) and show max/min quantities per category.

DEFINE
  // define a new column so that it can be used in SUMMARIZECOLUMNS
  COLUMN 'Product'[Price Group] = 
    VAR _MedianListPrice = [Median List Price]
    RETURN
    IF(
      'Product'[List Price] > _MedianListPrice,
      "High Priced",
      "Low Priced"
    )
  MEASURE 'Sales'[Max Quantity] = MAX('Sales'[Order Quantity])
  MEASURE 'Sales'[Min Quantity] = MIN('Sales'[Order Quantity])
EVALUATE
  SUMMARIZECOLUMNS(
    'Product'[Price Group],
    "Max Quantity",
    [Max Quantity],
    "Min Quantity",
    [Min Quantity]
  )
  ORDER BY 'Product'[Price Group] ASC

Key Concepts:

  • COLUMN defines a calculated column in DEFINE block
  • Calculated columns can be used as groupby columns in SUMMARIZECOLUMNS
  • Multiple measures defined and used in same query
  • IF expression for conditional logic

Summary

This guide covers the essential rules and patterns for writing valid DAX queries. Key takeaways:

  1. Always use ORDER BY when returning multiple rows
  2. Store measure results in variables before using in boolean filters
  3. Choose the right function: SUMMARIZECOLUMNS for measures, SUMMARIZE for distinct values, GROUPBY for table variables
  4. Establish date context for time intelligence functions
  5. Use renamed columns in ORDER BY after SELECTCOLUMNS
  6. Leverage table variables as filters for cleaner, more maintainable code

Practice these patterns to write efficient, readable DAX queries that follow best practices.

Raw
dax_udf_instructions_and_examples.md
name description uriTemplate
DAX UDF Instructions and Examples
Guidelines for creating Power BI DAX user-defined functions (UDFs)
resource://dax_udf_instructions_and_examples

DAX User-Defined Functions (UDFs) Guide

Overview

DAX User-Defined Functions (UDFs) allow you to create reusable function definitions in Power BI semantic models. This guide explains the syntax, type system, and best practices for defining UDFs.

Basic Syntax

A UDF definition consists of a function name and a function definition with parameters and a body:

FunctionName: MyFunction
FunctionDefinition: |-
  (param1 [: Type [Scalar Subtype] [Val|Expr]],
   param2 [: Type [Scalar Subtype] [Val|Expr]],
   ...
  ) =>
      <Function body>

Type System

Parameter Types

DAX UDFs support three main parameter types:

  • Scalar: A single value (number, text, date/time, boolean)
  • Table: A DAX table expression
  • AnyRef: A direct reference to an existing semantic model object without pre-evaluation

Scalar Subtypes

When using Scalar type, you can optionally specify a subtype:

  • Int64: Integer values
  • Decimal: Decimal numbers
  • Double: Double-precision floating-point numbers
  • String: Text values
  • DateTime: Date and time values
  • Boolean: True/false values
  • Numeric: Any numeric type (Int64, Decimal, or Double)
  • Variant: Any scalar type (use when the expression may yield different types)

Note: BLANK() is valid for any subtype.

AnyRef Type

Use AnyRef when you need a direct reference to a model object rather than its evaluated value. This is useful for functions that need to pass references to functions like CALCULATE, TREATAS, or SAMEPERIODLASTYEAR.

Allowed reference forms:

  • Column reference: 'Table'[Column]
  • Table reference: 'Table'
  • Measure reference: [Measure]
  • Calendar reference: MyCalendar

Parameter Modes

Parameters can be evaluated in two modes:

  • Val (value mode - default): The argument expression is evaluated at the call site before entering the function. The resulting value is substituted wherever the parameter is used.
  • Expr (expression mode): The raw argument expression is substituted into the function body and evaluated in its inner context. Use this when you want the expression to be re-evaluated within inner contexts created by CALCULATE, FILTER, or iteration functions.

Example Schema

The following examples reference this sample data model:

Tables:
  - Name: Sales
    Measures:
      - Name: Total Amount
        Type: Decimal
      - Name: Total Quantity
        Type: Integer
    Columns:
      - Name: CustomerKey
        Type: Text
      - Name: ProductKey
        Type: Text
      - Name: OrderDate
        Type: Date
  - Name: Product
    Columns:
      - Name: ProductKey
        Type: Text
      - Name: Name
        Type: Text
      - Name: Color
        Type: Text
  - Name: Customer
    Columns:
      - Name: CustomerKey
        Type: Text
      - Name: Name
        Type: Text
  - Name: Calendar
    Columns:
      - Name: Date
        Type: Date
      - Name: Month
        Type: Text
        SortByColumnName: MonthNumberOfYear
      - Name: MonthNumberOfYear
        Type: Integer
      - Name: Year
        Type: Integer
Active Relationships:
  - PK: 'Product'[ProductKey]
    FK: 'Sales'[ProductKey]
    Unidirectional Filter Propagation: "'Product' filters 'Sales'"
  - PK: 'Customer'[CustomerKey]
    FK: 'Sales'[CustomerKey]
    Unidirectional Filter Propagation: "'Customer' filters 'Sales'"
  - PK: 'Calendar'[Date]
    FK: 'Sales'[OrderDate]
    Unidirectional Filter Propagation: "'Calendar' filters 'Sales'"

Examples

Example 1: Simple Numeric Calculation

Calculate the area of a circle from its radius.

FunctionName: CircleArea
FunctionDefinition: |-
    (radius : Scalar Numeric) =>
        PI() * radius * radius

Usage: CircleArea(5) returns approximately 78.54

Example 2: Basic Value Transformation

Double an input value.

FunctionName: DoubleValue
FunctionDefinition: |-
    (inputValue : Scalar Numeric Val) =>
        inputValue * 2

Usage: DoubleValue(10) returns 20

Example 3: Working with AnyRef - Statistical Function

Returns the most frequently occurring value in a column.

FunctionName: Mode
FunctionDefinition: |-
    (tab : AnyRef,
     col : AnyRef
    ) =>
        MINX(
            TOPN(
                1,
                ADDCOLUMNS(
                    VALUES(col),
                    "Freq", CALCULATE(COUNTROWS(tab))
                ),
                [Freq], DESC
            ),
            col
        )

Explanation: This function uses AnyRef for both parameters because it needs to pass the table and column references to DAX functions like VALUES and CALCULATE. The function finds the value that appears most frequently by counting occurrences.

Usage: Mode('Sales', 'Sales'[ProductKey])

Example 4: Using Expr Mode for Time Intelligence

Evaluate any scalar expression in the prior year.

FunctionName: PriorYearValue
FunctionDefinition: |-
    (expression : Scalar Variant Expr,
     dateColumn : AnyRef
    ) =>
        CALCULATE(
            expression,
            SAMEPERIODLASTYEAR(dateColumn)
        )

Explanation: The expression parameter uses Expr mode so it's evaluated within the CALCULATE context with the prior year filter applied. The dateColumn uses AnyRef to pass the column reference to SAMEPERIODLASTYEAR.

Usage: PriorYearValue([Total Amount], 'Calendar'[Date])

Example 5: Returning a Table Filter

Return today's date as a one-row table for filtering.

FunctionName: TodayAsDate
FunctionDefinition: |-
    () =>
        TREATAS(
            { TODAY() },
            'Date'[Date]
        )

Explanation: This function demonstrates a UDF that returns a table. It uses TREATAS to convert the single-value table into a filter compatible with the Date table.

Usage: CALCULATE([Total Amount], TodayAsDate())

Example 6: Table-Returning Function

Return a table of the top 3 Products by the [Sales] measure.

FunctionName: Top3ProductsBySales
FunctionDefinition: |-
    () =>
        TOPN(
            3,
            VALUES('Product'[ProductKey]),
            [Sales], DESC
        )

Explanation: This parameterless function returns a table containing the top 3 products. It can be used anywhere a table expression is expected.

Usage: CALCULATE([Total Amount], Top3ProductsBySales())

Example 7: String Manipulation with Table Return

Split a text by a delimiter and return a single-column table.

FunctionName: SplitString
FunctionDefinition: |-
    (s : Scalar String,
     delimiter : Scalar String
    ) =>
    VAR str =
        SUBSTITUTE(s, delimiter, "|")
    VAR len =
        PATHLENGTH(str)
    RETURN
        SELECTCOLUMNS(
            GENERATESERIES(1, len),
            "Value", PATHITEM(str, [Value], TEXT)
        )

Explanation: This function uses variables (VAR) and demonstrates how to build complex logic. It converts the delimiter to a path separator, counts the parts, and returns a table with each part as a row.

Usage: SplitString("apple,banana,cherry", ",")

Best Practices

  1. Use appropriate type hints: Specify types and subtypes to make your functions more robust and self-documenting
  2. Choose the right parameter mode: Use Expr when you need the expression to be evaluated in the function's context, otherwise use Val (default)
  3. Use AnyRef for references: When passing columns, tables, or measures to DAX functions that expect references, use AnyRef
  4. Document your functions: Include clear descriptions of what each function does
  5. Test with edge cases: Consider BLANK values and empty tables in your function logic
  6. Keep functions focused: Each function should have a single, well-defined purpose
  7. Use variables: For complex functions, use VAR to break down logic and improve readability
Raw
powerbi_project_instructions.md
name description uriTemplate
PowerBI Project Instructions
Instructions for structuring Power BI projects
resource://powerbi_project_instructions

You are an expert in Power BI Project (PBIP) file structure.

If the powerbi-modeling-mcp MCP server is available, do not create or edit the TMDL files directly.

PBIP structure

root/
├── [Name].SemanticModel/
|   ├── /definition # The semantic model definition using TMDL language
|   ├──  definition.pbism # The semantic model definition file
├── [Name].Report/        
|   ├── /definition # The report definition using PBIR format
|   ├──  definition.pbir # The report definition file with a byPath relative reference to the semantic model folder
└── [Name].pbip # A shortcut file to the report folder

Example of a definition.pbism file

No modifications are needed—just create the file exactly as shown in the example.

{
    "$schema": "https://developer.microsoft.com/json-schemas/fabric/item/semanticModel/definitionProperties/1.0.0/schema.json",
    "version": "4.2",
    "settings": {
        "qnaEnabled": true
    }
}

Example of a definition.pbir file

The byPath property should reference the semantic model folder using a relative path like below.

{
    "$schema": "https://developer.microsoft.com/json-schemas/fabric/item/report/definitionProperties/2.0.0/schema.json",
    "version": "4.0",
    "datasetReference": {
        "byPath": {
            "path": "../{Name of the Semantic Model}.SemanticModel"
        }
    }
}

Example of a {Name of the Semantic Model}.pbip file

{
    "$schema": "https://developer.microsoft.com/json-schemas/fabric/pbip/pbipProperties/1.0.0/schema.json",
    "version": "1.0",
    "artifacts": [
        {
        "report": {
            "path": "{Name of the Semantic Model}.Report"
        }
        }
    ],
    "settings": {
        "enableAutoRecovery": true
    }
}

Open from PBIP

When asked to open/load the semantic model from a PBIP, you must only load the [Name].SemanticModel/definition folder. No other folder is suitable to load from semantic model developer tools.

Save to PBIP

When asked to save to a new PBIP folder make sure you create the folder and files from the structure above using the provided examples.

Creation of new semantic model

  1. Create a PBIP folder for the semantic model following the structure above
  2. Use the database_operations tool of the MCP server to Create a new database.
  3. In the end of the modeling session, serialize as TMDL to the definition/ folder in the PBIP
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment