Forum Discussion
Hierarchy Slicer Metadata for Auto Date/Time Hierarchies in Power BI Embedded
We are using getSlicerState() to retrieve hierarchy slicer selection details.
For hierarchy slicers, the API currently returns target like:
{
"table": "FactSales",
"hierarchy": "Date Hierarchy",
"hierarchyLevel": "Year"
}However, we have encountered an ambiguity with auto-generated date hierarchies.
Scenario:
A table can contain multiple date columns (e.g., OrderDate, ShipDate, InvoiceDate).
With Auto Date/Time enabled, Power BI creates separate hidden LocalDateTables for each date column.
Each LocalDateTable contains a hierarchy named "Date Hierarchy".
When a hierarchy slicer is configured on one of these auto-generated date hierarchies, getSlicerState() returns only the table name, hierarchy name, and hierarchy level.
- A table can also contain a custom date type hierarchy called "Date Hierarchy".
As a result, multiple distinct hierarchies can appear identical in the slicer state payload because they all resolve to:{
"table": "<table name>",
"hierarchy": "Date Hierarchy"
}Question:
Is there any supported API, metadata endpoint, or slicer property that allows us to identify the underlying source date column or LocalDateTable associated with the selected hierarchy?
Specifically, can we retrieve any of the following?
LocalDateTable name or ID corresponding to the selected hierarchy slicer.
Source date column name or ID (e.g., OrderDate, ShipDate, InvoiceDate).
Any visual metadata available through Power BI Embedded APIs that can be used to uniquely identify the hierarchy source.
Any guidance on supported approaches or recommended alternatives would be greatly appreciated.
Hi Shubhangi18 ,
Thank you for your follow up.
To clarify, when referring to a dedicated Date table and custom hierarchies, you would retain your existing columns Column A, Column B, and Column C as they are, without removing or migrating any data. There is no risk of data loss.
The main issue is that Auto Date Time generates hidden date tables and hierarchies, which are exposed in the Embedded API only as Date Hierarchy, without linking back to the original columns. Since these LocalDateTables are not accessible via the API, it is not currently possible to determine whether a selection originated from Column A, Column B, or Column C.
The recommended approach is to modify the report's model by creating custom hierarchies with distinct names and using those in the slicer. For example, you could define separate hierarchies for Order Date, Ship Date, and Invoice Date. These custom hierarchies are identifiable through the API, enabling you to pinpoint the source context.If your report currently uses Auto Date Time hierarchies, there is no supported API method to map a hierarchy slicer back to its underlying date column. In this case, updating the report to use custom date hierarchies is the only reliable solution to avoid ambiguity.
In summary, the recommendation is to keep your existing date columns and replace the Auto Date Time hierarchy objects with custom hierarchies that can be uniquely identified through the API.
Thank you.
6 Replies
Hi Shubhangi18 ,
Thanks for reaching out to the Microsoft fabric community forum.
I have reviewed the Power BI Embedded APIs and the behavior of Auto Date Time hierarchies. At present, there is no supported API that exposes the underlying source date column or the hidden LocalDateTable linked to an Auto Date Time hierarchy slicer.Using getSlicerState() on a hierarchy slicer only provides details such as the table name, hierarchy name, and hierarchy level. For Auto Date Time hierarchies, Power BI generates hidden LocalDateTables, but these are not accessible via the Embedded JavaScript APIs. As a result, if a table has multiple date columns, each with its own Auto Date Time hierarchy, the returned metadata appears identical and does not indicate the originating source column.
Additionally, there is currently no method through getSlicerState(), getFilters(), getVisuals(), or other Power BI Embedded APIs to retrieve the LocalDateTable name, ID, source date column name, or any metadata that uniquely identifies the source column.
To address this, if you need to distinguish between multiple date hierarchies in your embedded solution, it is recommended to use a dedicated date table and create custom hierarchies. Custom hierarchies have explicitly defined names, allowing them to be uniquely identified through the API metadata and eliminating the ambiguity associated with Auto Date Time hierarchies.
For further information, please refer to the following Microsoft documentation:
Auto date/time guidance in Power BI Desktop:
https://learn.microsoft.com/en-us/power-bi/guidance/auto-date-timeThis is a known limitation of the current API, rather than an implementation issue.
Best Regards,
Tejaswi.
Community SupportHi Tejaswi,
Thankyou for the confirmation and documentation link. Quite helpful!
Can you please elaborate more on the "To address this, if you need to distinguish between multiple date hierarchies in your embedded solution, it is recommended to use a dedicated date table and create custom hierarchies." part ?
Lets say if users already have a table called Table A with 3 date columns Column A,B,C. These are separate date hierarchies.
Now what is the suggested workaround without existing data loss?Hi Shubhangi18 ,
Thank you for your follow up.
To clarify, when referring to a dedicated Date table and custom hierarchies, you would retain your existing columns Column A, Column B, and Column C as they are, without removing or migrating any data. There is no risk of data loss.
The main issue is that Auto Date Time generates hidden date tables and hierarchies, which are exposed in the Embedded API only as Date Hierarchy, without linking back to the original columns. Since these LocalDateTables are not accessible via the API, it is not currently possible to determine whether a selection originated from Column A, Column B, or Column C.
The recommended approach is to modify the report's model by creating custom hierarchies with distinct names and using those in the slicer. For example, you could define separate hierarchies for Order Date, Ship Date, and Invoice Date. These custom hierarchies are identifiable through the API, enabling you to pinpoint the source context.If your report currently uses Auto Date Time hierarchies, there is no supported API method to map a hierarchy slicer back to its underlying date column. In this case, updating the report to use custom date hierarchies is the only reliable solution to avoid ambiguity.
In summary, the recommendation is to keep your existing date columns and replace the Auto Date Time hierarchy objects with custom hierarchies that can be uniquely identified through the API.
Thank you.Hi Shubhangi18 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.
- anilelmastasi
Super User
Hello ,
No, there is currently no supported API. You should resolve the ambiguity at the data-modeling layer. If you use role-playing dimensions (physical tables like OrderDateCalendar and ShipDateCalendar), getSlicerState() will return uniquely identifiable targets. You can address like
"table": "OrderDateCalendar", "hierarchy": "Calendar Hierarchy", "hierarchyLevel": "Year"
Please check role playing dimensions.
If this solved your issue, please mark it as the accepted solution. ✅
