Htykuut

I have two problems:

1. Transform a query to DAX syntax


2. Add a Dropdown DateTime parameter that can filter data into the report in PBI

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 t

WHERE  CalculeDansCapacite = 2 

AND NOT UniteSoinCode IN ('CDJ','CDJN','SOP','SOPN')

AND DateDebutChambreLitSejour < @DateTimeOfReport

AND @DateTimeOfReport <= ISNULL(DateFinChambreLitSejour, @DateTimeOfReport )

GROUP BY Installation

Questions :

• How to replace in PBI the '2018-11-28 10:00' by the result of the drop-down menu?




• Or is it a way to transform that query in DAX syntax?