Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Team,
I'm want to restrict the data to the users. So, i have created a Date parameter wherein user have to select Start date and End Date in Power bi service.
I have created below M Langauage and pasted in Advance Editor
Next I have Created two Parameters Param1 and Param2
let
SQLSOURCE=(Param1 as date, Param2 as date)=>
let
Param1=Date.ToText(Param1,"MM")&"/"&Date.ToText(Param1,"dd")&"/"&Date.ToText(Param1,"yyyy"),
Param2=Date.ToText(Param2,"MM")&"/"&Date.ToText(Param2,"dd")&"/"&Date.ToText(Param2,"yyyy"),
Source=Sql.Database("sqllink","Database",
[Query="Select Column1,Column1,Date_Txt from (SELECT Column1,Column2,LEFT(CONVERT(VARCHAR, Date_N, 120), 10) as Date_Txt FROM dbo.Transform )a
where Date_Txt >='"&Param1&"' AND Date_Txt <='"&Param2 & "'"])
in
Source
in
SQLSOURCE
When i run the query i am not getting any results.
Please guide me how to create parameters in date level and add in power bi dataset.
Thank you
Ravi
Hi @Anonymous ,
Basically it should work. Do you have tried this with the latest PBI Desktop ?
Here is the real working sample video : https://channel9.msdn.com/Blogs/MVP-Azure/Pass-parameter-to-SQL-Queries-statement-using-Power-BI
I passed the exact syntax to my SQL query for the data parameter, '"StartTime"' and I received a token Comma syntax error in the Advanced Editor. Both the Parameters are Date data types. Any ideas?
Hi Jayendran,
Thank you for your reply on this...
I Have created parameters as showed in your video it is working in Power Bi desktop. However, when i go to Dataset -- Settings -- Parameters. Over there in StartDate and EndDate i have give date range. As per the given date range data is not populating in power bi service. My i know the reason... In Desktop it is working fine but when comes to PBI Services why data is not populating as per given range....
Please clarify..
Thanks in adavance.
RAVIP
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
2 | |
2 | |
1 | |
1 |