I have two problems:
I am working with PBI in DirectQuery mode connected to a SQL Server Database. I need to filter my report by selecting the DateTimeOfReport in a drop-down menu. The first step is to link the report to the following SQL Server query. I try to use the advanced option with SQL Statement for the following query:
DECLARE @DateTimeOfReport datetime = '2018-11-28 10:00' SELECT Installation , COUNT(DISTINCT t.NoAdmission) Cnt , COUNT(DISTINCT CASE ServiceCode WHEN 'PSY' THEN t.NoAdmission ELSE NULL END) PsyCnt , COUNT(DISTINCT CASE ServiceCode WHEN 'PSY' THEN NULL ELSE t.NoAdmission END) NonPsyCnt-- SELECT * FROM AdtHndSov.ChambreLitSejour tWHERE CalculeDansCapacite = 2 AND NOT UniteSoinCode IN ('CDJ','CDJN','SOP','SOPN') AND DateDebutChambreLitSejour < @DateTimeOfReportAND @DateTimeOfReport <= ISNULL(DateFinChambreLitSejour, @DateTimeOfReport ) GROUP BY Installation
How to replace in PBI the '2018-11-28 10:00' by the value of the drop-down menu on a PBI report ?
Or is it a way to transform that query in DAX syntax?
@Zebel Please try using "Parameters" in Power Query Editor and then filter the data by using the dateParameter that was created. Option available to filter by parameter in Power Query (refer screenshot below)