cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Zebel Frequent Visitor
Frequent Visitor

Convert SQL Query with Declare statment to DAX syntax

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.

2 REPLIES 2
Super User
Super User

Re: Convert SQL Query with Declare statment to 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)

 

image.png



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Zebel Frequent Visitor
Frequent Visitor

Re: Convert SQL Query with Declare statment to DAX syntax

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 333 members 3,050 guests
Please welcome our newest community members: