Calling a parametrized UDF using filter/slicer?

[rajatparihar]

Hi, I have a UDF function (udf_EvaluateAggregatedFormula) as follows and would like to call it using a sql statement in Power BI.

However if you see, my UDF has a date parameter (@DataReportingDat@@, ) which should set value from a date slicer. Can it be done?

dbo.udf_EvaluateAggregatedFormula

(

 SGL.InstitutionGroupCode, --InstitutionGroupCode

@DataReportingDate, --@ReportingPeriod DATETIME,


) EAF

[v-juanli-msft]

Hi @rajatparihar

Though I'm not quite sure if it can works exactly as you required in Power BI, I would suggest you consider dynamic parameter, it can be used when getting data, applying a filter, ect.

http://biinsight.com/power-bi-desktop-query-parameters-part-1/

https://www.excelguru.ca/blog/2018/05/30/creating-dynamic-parameters-in-power-query/

Best Regards

Maggie

[rajatparihar]

Actually my doubt is bit different as I want to pass the user defined parameter value to a query and based on that I would like to see the results. Have a look at the following pseudo code that I prepared to expalin my query in better way:

You can see that I have declared a static variable @DataReportingDate and would like to use this parameter inside my query in left join and in select statement.

Declare @DataReportingDate date = {D'2017-06-30'}  --it should be any single select date that I want to pass to the following query inside left join

SELECT
a.name,
a.address,
a.eircode,
b.employername,
b.salary,
b.ID,
@DataReportingDate as 'Reporting Date'		
FROM TableA a

LEFT OUTER JOIN (select salary, employername, ReportingDate from TableB 
				 where ReportingDate = @DataReportingDate ) b
	ON a.ID = b.ID