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
Retirement of Power BI Q&A
Power BI Q&A, the legacy natural language tool, will retire by December 2026. New Q&A visuals cannot be created, and existing ones w...
Power BI: End of support for on-prem SharePoint web part
Support for the SSRS Report Viewer SharePoint Webpart ends April 13, 2026. It will remain functional but unsupported. Organizations should tra...
Measuring Geocoding Performance In Power BI Map Visuals Using Performance Analyzer
When a user complains about their Power BI report being slow it’s normal for the developer to open up DAX Studio and start tuning the me...
The Power BI Dataviz World Champs is back!
Join the Power BI Dataviz World Championships – three rounds of visualization challenges, community voting, and a live finale at FabCon ...
Power BI Report metadata defaults to Power BI Enhanced Report format (PBIR)
Starting January 25, 2026, Power BI reports will default to the Enhanced Report format (PBIR), improving source control and collaboration. Exi...
Power BI Progress Bar You Haven't seen Before
Diagnosing Power BI DirectQuery Performance Problems Caused By SQL Queries That Return Large Resultsets
One very common cause of Power BI performance problems is having a table with a large number of rows on your report. It’s a problem I wr...