Filter a report using query string parameters in the URL, the value is coming from a Date Hierarchy

[alianazambori]

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

[alianazambori]

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

[alianazambori]

Thank you! The solution works perfectly.

[v-kkf-msft · Accepted solution]

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.

[Anonymous]

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