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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
purple_banana
Frequent Visitor

User-defined Parameters with Dataflows (Direct Query)

I have a DQ connection to a dataflow, and am successfully able to use parameters to limit the returned rows. However when I bind a field to that parameter (link), the query fails. I have verified that the field, parameter and dataflow field all share the same datatype (Date) and that the values I am using are valid.

 

I noticed in the Advanced Editor, the line referring to the parameter looks like this:

 

#"Filtered Rows1" = Table.SelectRows(#"Removed Columns1", each [_Date] >= LoadOnOrAfter)

 

 

But when I hard-code a date, it looks like this:

 

#"Filtered Rows2" = Table.SelectRows(#"Removed Columns1", each ([_Date] = #date(2022, 4, 11)))

 

 

I have also attempted to modify that hard-coded example and pass in three integer parameters, but this does not work either:

 

#"Filtered Rows2" = Table.SelectRows(#"Removed Columns1", each ([_Date] = #date(Year, Month, Day)))

 

 

Please can anyone advise what I might be doing wrong?

 

 

TIA

 

2 REPLIES 2
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

Hi @purple_banana - I think you need to check that value passed from Slicer (i.e. LoadOnOrAfter) is a Date.  It might be text or datetime.  You may need to convert the value using a Date.From() function.  Also double-check that the [_Date] is Date.Type, it might be expecting a DateTime.Type.   For the 3 formula, could you please provide the steps where the Date was converted to Year, Month and Day.

Thanks for your reply, Daryl.

 

I have verified that the datatype for all relavent fields & parameters are the same - 'Date'. I even tried changing the parameter to DateTime which resulted in a failure due to the mismatched datatypes (Date vs DateTime).

 

I tried converting the parameter using Date.From(LoadOnOrAfter), that didn't make any difference.

 

There aren't any other steps for my attempt to pass in the Year, Month & Day parameters, they are just configured as three numeric parameters.

 

I think the issue is to do with the binding of fields to the parameters. If I unbind them and set the values via Manage Parameters, the queries work perfectly. It is only when I bind them to fields in my Calendar table that the queries fail.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.