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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors