Forum Discussion
Filter a report using query string parameters in the URL, the value is coming from a Date Hierarchy
Hello,
I need to build in a URL a filter by Month when the Month is coming from a Date column/ Date Hierarchy. Here is a screenshot of the slicer and I have to pass the month of July for example in the URL:
Table Name: Dates
Column Name: Date
Hierarchy name: Date Hierarchy
Filter criteria from Hierarchy: Month
Example for filter value: July
This is what I am trying to achieve: PBI_link_with_any_filter&filter=Dates%2FMonth%20eq%20%27July%27 (this part that it is in bold I don't know how to build it).
Thank you
Hi alianazambori ,
Because the date hierarchy is derived from a hidden table in the model, you cannot refer to it directly in the query parameters using Date.Month. You need to use SSMS/DAX Studio to find that hidden table name and then filter it, for example: ?filter=LocalDateTable_67609027-3022-4eea-905a-9c089f72e640%2FMonth%20eq%20%27July%27
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi alianazambori ,
Because the date hierarchy is derived from a hidden table in the model, you cannot refer to it directly in the query parameters using Date.Month. You need to use SSMS/DAX Studio to find that hidden table name and then filter it, for example: ?filter=LocalDateTable_67609027-3022-4eea-905a-9c089f72e640%2FMonth%20eq%20%27July%27
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.I have a very similar issue, below is a screenshot of the slicer and I could not find any hidden tables
But the Date Dimension table has been created using the below logic:
Date Dimension =
ADDCOLUMNS (
CALENDAR (MIN('Fact table'[date]), MAX('Fact table'[date])),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthDatenumber", FORMAT ( [Date], "YYYY/MM/DD" ),
"YearMonthShort", FORMAT ( [Date], "MMM-YY" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"WeekNumber", WEEKNUM([Date], 2),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ))
Could you help with my issue, thanks is advance
Thank you! The solution works perfectly.
Hi,
To identify that hidden table I was using the Vertipaq Analyzer (might be another solution too). So I added in External Tools the tool called DAX Studio
then I used Vertipaq Analyzer: https://www.sqlbi.com/tools/vertipaq-analyzer/ with the guide on how to do it further here: https://www.sqlbi.com/articles/data-model-size-with-vertipaq-analyzer/
You will see following the instruction that an .xlsm file will be generated:
Inside this file, you will find how the Date table is called. In the Tables Tab (first tab) you will have something similar to this: DateTableTemplate_c0dbb16f-5245-4b73-91ce-cca98ae74a08
So in your linked you'll be using: &filter=DateTableTemplate_c0dbb16f-5245-4b73-91ce-cca98ae74a08%2FMonth%20eq%20%27July%27
Regards,
Liana
