Why DAX Can't Directly Reference Hierarchies in Power BI?
📌 What is a Hierarchy in Power BI?
A hierarchy in Power BI is a structured arrangement of related columns that allows users to drill down from a higher-level category to a more detailed level.
🔹 Example of a Hierarchy
Consider a Date Hierarchy:
- Year → Quarter → Month → Day
Or a Geography Hierarchy:
- Country → State → City
🚫 Why Can’t DAX Refer to a Hierarchy or Hierarchy Level?
DAX can only reference columns, measures, and tables, but not hierarchy objects directly. This is because a hierarchy is a UI feature in Power BI, not an actual data model object.
However, you can reference each level of the hierarchy individually using its column name.
✅ Alternative Approach Using DAX
If you want to calculate something for a specific level in a hierarchy, you must use the underlying column:
🔸 Example: Total Sales by Year (from Date Hierarchy)
TotalSalesByYear = CALCULATE(SUM(Sales[Revenue]), VALUES(Sales[Year]))
This formula references Sales[Year], not the entire hierarchy.
Published on:
Learn more
Related posts
Power BI, Build Permissions And Security
If there is sensitive data in your Power BI semantic model that you don’t want some users to see then you need to use row-level security...
Free Power BI Starter Kit (PBIX + Excel + Roadmap) – Download & Start in 30 Minutes
If you’ve always wanted to learn Power BI but didn’t know where to start, this Free Power BI Starter Kit is the perfect first step. Whether yo...
How to Access a Former Employee’s Power BI “My Workspace” and Recover Reports
One of the common challenges I’ve seen in organizations is when a team member leaves and their Power BI reports are stored in their personal M...
Standalone Copilot in Power BI mobile apps: Chat with your data anytime anywhere (Preview)
We’re excited to announce the release of the Standalone Copilot experience in Power BI Mobile apps! Copilot in Power BI on mobile is a new way...
Power BI Update - November 2025
Bringing Context Aware Intelligence to Power BI
Ten years ago, Power BI entered the market and transformed how businesses interact with data. Before self-service BI, business users waited da...
Power BI November 2025 Feature Summary
The November 2025 Power BI feature update brings several important announcements and enhancements across the platform. Key highlights include ...
PBIR will become the default Power BI Report Format – Get Ready for the Transition
Starting in January 2026, all new reports created in both Power BI Service and Desktop will use the PBIR format by default, and existing repor...