Forum Discussion

alianazambori's avatar
alianazambori
Frequent Visitor
4 years ago
Solved

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 ...
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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.

alianazambori's avatar
alianazambori
Frequent Visitor
4 years ago

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