Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
LD2022
Helper II
Helper II

Parameter/Bind variables based on Date Calendar Input

The requirement is to restrict the direct query table rows based on Start and End Date calendar inputs as it is a huge transaction table.

Created 2 Calendar tables using DAX :

Start Date - Calendar(date(2012,1,1),TODAY ())

End Date - Calendar(date(2012,1,1),TODAY ())

Bounded to variables

StartDt

Enddt respectively.

However, the users need the flexibility to select the range using Calendar Date picker slicer. Calendar option is disabled for the columns bound to parameters.

 

I also tried to create a date table Range that will be used for selecting the range.

I created another table that only holds MIN and MAX dates

CALENDAR( MIN(Range[Date]), MAX(Range[Date]))

but MIN and MAX values are showing the range based on all records instead of selected slicer date range.

Is it possible to create columns (not measure as it cannot be linked with variable) that holds min and max date that can be used to initialize a variable?

5 REPLIES 5
v-jingzhang
Community Support
Community Support

Hi @LD2022 

 

It seems you are using the Dynamic M query parameter feature. Please refer to the following document to check if your settings have hit one or multiple limitations or unsupported scenarios when using this feature. 

Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

 

At present it seems your table names have hit below limitation: 

  • Table names can't contain spaces or special characters.

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

I confirm there is no space in the table name. 

Start_date and End_date are 2 calendar DAX tables and date columns are linked to parameters. But I want to use 1 calendar input slicer to selecf dates between and pass the range as parameter, is it possible?

 

 

Hi @LD2022 

 

I'm afraid this is not supported as it would hit another limitation:

  • A single parameter can't be bound to multiple fields nor vice-versa.

In a date slicer, it only has one date field, which cannot be bound to two paramters. 

 

Best regards,

Jing

Is there any other workaround? I also tried to create a calculated column based on date selection i.e. FIRSTDATE() and LASTDATE() and linked it with variable but had no luck, would you know if I can create a date_table2 that will hold MAX and MIN dates of date_Table1 , when a date range is selected in date_Table1, table2 Max and Min columns will be filtered and hold the start and end range values? 

LD2022
Helper II
Helper II

Please help and happy to provide sample data.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors