Convert SQL Query with Declare statment to DAX syntax

[Zebel]

Hi !!

I have two problems:

1. Transform a query to DAX syntax
2. Add a Dropdown DateTime parameter that can filter data on the report according to the value of the selected parameter 

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

Questions :

• How can I link  the DECLARE @DateOfTimeOfReport with a PBI Measure or Parameter ?

• 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?

Thanks.

[PattemManohar]

@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)

[Zebel]

Thank you for your reply dear @PattemManohar. I found quite interesting your advice but I didn't know how to do that. A quick search on YouTube help me to found the link below for parameters un PBI deskop:
https://youtu.be/0B3kmAsEJHY

So, one step is done. The next level is to convert SQL query into DAX syntax. Thank you by advance.