Date parameter not working

[TurgayEsen]

I want filtering by using dynamic date parameter in Powerbi. The parameter will be used in a PL/SQL query. Parameter in query (APPS.XXXT_DISCO_PRM.SET_FIRST_DATE(TO_DATE('"& Date.ToText(pdate) & "','DD.MM.YYYY')))) . The parameter I created is as follows (first pic). I also share the advanced editor screen below. When I manually change the parameter value in Power Query, there is no problem and it works fine.

I created a table called date_ and I want to retrive parameter values from this table

date_ = CALENDAR(DATE(2023,1,1),DATE(2023,3,1))

date_ bind to pdate parameter is fine

Problem : 

I created a slicer from the date_ table , pick a date then it is error.

let
filterquery = "SELECT o116518.SICIL_NO as Sicil_No,
o116518.ISIM as Ad,
o116518.SOYISIM as Soyad,
o116518.CALISMA_YERI as CalismaYeri,
o116518.GOREV as Gorev,
o116518.BOLUM as Bolum,
o116518.POZISYON as Pozisyon,
o116518.HIZMET_TURU_2 as HizmetTutu2,
o116518.ISE_BASLAMA_TARIHI as IseBaslamaTarihi,
o116518.KIDEME_BASLAMA_TARIHI as E210353,
o116518.MALIYET_MERKEZI as MaliyetMerkezi,
o116518.CALISMA_GRUBU as CalismaGrubu,
o116518.HIZMET_TURU as HizmetTuru,
o116518.KADEME as Kademe,
o116518.IS_AILESI as IsAilesi,
o116518.UPPER_ORGANIZATION as Direktorluk,
o116518.DATE_OF_BIRTH as E4582626

FROM (SELECT sicil_no,
isim,
soyisim,
ise_baslama_tarihi_kazanclar,
ise_baslama_tarihi,
kideme_baslama_tarihi,
bolum,
gorev,
pozisyon,
kademe,
izin_referans_tarihi, --2907 G.K.
calisma_yeri,
maliyet_merkezi,
departman,
is_ailesi,
calisma_grubu,
hizmet_turu,
MAX(cift_sicil_no_1) cift_sicil_no_1,
MAX(cift_sicil_no_2) cift_sicil_no_2,
MAX(cift_sicil_no_3) cift_sicil_no_3,
MAX(cift_sicil_no_4) cift_sicil_no_4,
MAX(hangi_sirkete_ait_1) hangi_sirkete_ait_1,
MAX(hangi_sirkete_ait_2) hangi_sirkete_ait_2,
MAX(hangi_sirkete_ait_3) hangi_sirkete_ait_3,
MAX(hangi_sirkete_ait_4) hangi_sirkete_ait_4,
hizmet_turu_2,
MAX(cep_telefonu) cep_telefonu,
upper_organization, -->210319 bkorkmaz RITM0086405
gruba_ilk_giris_tarihi --gguner 10022020 INC0238923
,
date_of_birth,
national_identifier,
tckn
FROM (SELECT t1.*,
CASE
WHEN rownumber = 1 THEN
cift_sicil_no
END cift_sicil_no_1,
CASE
WHEN rownumber = 2 THEN
cift_sicil_no
END cift_sicil_no_2,
CASE
WHEN rownumber = 3 THEN
cift_sicil_no
END cift_sicil_no_3,
CASE
WHEN rownumber = 4 THEN
cift_sicil_no
END cift_sicil_no_4,
CASE
WHEN rownumber = 1 THEN
hangi_sirkete_ait
END hangi_sirkete_ait_1,
CASE
WHEN rownumber = 2 THEN
hangi_sirkete_ait
END hangi_sirkete_ait_2,
CASE
WHEN rownumber = 3 THEN
hangi_sirkete_ait
END hangi_sirkete_ait_3,
CASE
WHEN rownumber = 4 THEN
hangi_sirkete_ait
END hangi_sirkete_ait_4
FROM (SELECT per.employee_number sicil_no,
per.first_name isim,
per.last_name soyisim,
per.original_date_of_hire ise_baslama_tarihi_kazanclar,
NVL(pps.date_start, per.original_date_of_hire) ise_baslama_tarihi,
per.attribute1 kideme_baslama_tarihi,
hra.name bolum,
jobs.name gorev,
pos.name pozisyon,
pg.name kademe,
per.attribute8 izin_referans_tarihi,
per.attribute2 gruba_ilk_giris_tarihi,
hla.location_code calisma_yeri,
ass.ass_attribute1 maliyet_merkezi,
ass.ass_attribute2 departman,
ass.ass_attribute3 is_ailesi,
pay.segment1 calisma_grubu,
pay.segment2 hizmet_turu,
x.segment1 cift_sicil_no,
x.segment2 hangi_sirkete_ait,
pay.segment3 hizmet_turu_2,
(SELECT MAX(CASE
WHEN p.phone_type = 'M' THEN
p.phone_number
ELSE
NULL
END)
FROM per_phones p
WHERE per.person_id = p.parent_id) cep_telefonu,
(SELECT DISTINCT ppc.segment3
FROM hr.per_analysis_criteria ppc,
hr.per_person_analyses ppa
WHERE ppc.id_flex_num = 50568
AND ppa.id_flex_num = 50568
AND ppc.analysis_criteria_id =
ppa.analysis_criteria_id
AND ppc.end_date_active IS NULL
-- AND ppc.enabled_flag = 'Y'
AND ppa.person_id = per.person_id
and rownum = 1) upper_organization,
ROW_NUMBER() OVER(PARTITION BY per.first_name || per.last_name ORDER BY per.first_name || per.last_name) rownumber,
per.date_of_birth,
per.national_identifier,
(SELECT pac1.segment1
FROM hr.per_analysis_criteria pac1,
hr.per_person_analyses pan1
WHERE pac1.analysis_criteria_id(+) =
pan1.analysis_criteria_id
AND pac1.id_flex_num = 50371
AND pan1.person_id = per.person_id
and rownum = 1) tckn
FROM hr.per_jobs jobs,
hr.per_all_people_f per,
per_all_assignments_f ass,
hr.hr_all_organization_units hra,
hr_locations_all hla,
hr.per_all_positions pos,
hr.per_grades pg,
hr.pay_people_groups pay,
hr.per_periods_of_service pps,
--per_periods_of_placement ppp,--AAkcan bağlanmamıştı 17.07.2017
(SELECT person_id, pac.segment1, pac.segment2
FROM per_analysis_criteria pac,
per_person_analyses pan
WHERE pac.analysis_criteria_id(+) =
pan.analysis_criteria_id
AND pac.id_flex_num = 50387) x
WHERE 1 = 1
AND ass.person_id = per.person_id
AND pps.person_id(+) = per.person_id
AND pg.grade_id(+) = ass.grade_id
AND hla.location_id = ass.location_id
AND ass.position_id = pos.position_id(+)
AND ass.job_id = jobs.job_id(+)
AND ass.organization_id = hra.organization_id(+)
AND ass.people_group_id = pay.people_group_id(+)
AND per.person_type_id IN (1120, 5118, 3118)
AND x.person_id(+) = per.person_id
AND (pps.actual_termination_date IS NULL OR
pps.actual_termination_date >
apps.xxxt_disco_prm.get_first_date)
AND apps.xxxt_disco_prm.get_first_date BETWEEN
TO_DATE(TO_CHAR(ass.effective_start_date,
'dd.mm.yyyy'),
'dd.mm,yyyy') AND
TO_DATE(TO_CHAR(ass.effective_end_date,
'dd.mm.yyyy'),
'dd.mm,yyyy')
AND apps.xxxt_disco_prm.get_first_date BETWEEN
TO_DATE(TO_CHAR(per.effective_start_date,
'dd.mm.yyyy'),
'dd.mm,yyyy') AND
TO_DATE(TO_CHAR(per.effective_end_date,
'dd.mm.yyyy'),
'dd.mm,yyyy') -- and sysdate between per.EFFECTIVE_START_DATE and per.EFFECTIVE_END_DATE

) t1)
GROUP BY sicil_no,
isim,
soyisim,
ise_baslama_tarihi,
kideme_baslama_tarihi,
ise_baslama_tarihi_kazanclar,
bolum,
gorev,
pozisyon,
kademe,
izin_referans_tarihi,
gruba_ilk_giris_tarihi,
calisma_yeri,
maliyet_merkezi,
departman,
is_ailesi,
calisma_grubu,
hizmet_turu,
hizmet_turu_2,
upper_organization,
date_of_birth,
national_identifier,
tckn) o116518
WHERE (o116518.SICIL_NO NOT LIKE '600%')
AND (1 = APPS.XXXT_DISCO_PRM.SET_FIRST_DATE(TO_DATE('"& Date.ToText(pdate) & "','DD.MM.YYYY')))
ORDER BY o116518.SICIL_NO ASC"
,
Kaynak = Oracle.Database("ah_8070", [Query=filterquery])
in
Kaynak

[v-jingzhang · Accepted solution]

Hi @TurgayEsen 

You may try setting up your pdate parameter to Date/Time data type, then using Date.ToText(DateTime.Date(pdate)) in your M query. 

Reference: Considerations and limitations section from Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

I took SQL Server database for a test. When I set the parameter pDate as Date data type, I had an error similar to yours. However, when I set the parameter to Date/Time type, this error disappeared and everything worked as expected. Below is my test result. 

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