Convert DAX query to SQL

Devi_AK — Sat, 06 Jul 2024 01:49:53 GMT

Hello,

Can anyne help me to convert the below DAX query to SQL to run it in SSMS?

CALCULATE('Req'[Number of Req],

FILTER(Req ((RELATED( 'Date1'[ADate])< LASTDATE('Date2'[BDate]) || (RELATED('Date1'[ADate])=LASTDATE('Date2'[BDate]) && RELATED('Time1'[ATime])<=MAX('Time2'[BTime]))) &&

((IF(ISBLANK(RELATED('Date3'[CDate])),RELATED('Date4'[DDate]),RELATED('Date3'[CDate))> FIRSTDATE('Date4'[DDate])
|| (IF(ISBLANK(RELATED('Date3'[CDate])),RELATED('Date4'[DDate]),RELATED(('Date3'[CDate]))=FIRSTDATE(('Date4'[DDate]) && IF(ISBLANK(RELATED('Time3'[CTime])),RELATED('Time4'[DTime]),RELATED('Time3'[CTime])>=MIN('Time4'[DTime])))
|| (ISBLANK(RELATED('Time3'[CTime]))
&& ISBLANK(RELATED('Time4'[DTime]))))))))

Thank You 🙂

Re: Convert DAX query to SQL

aduguid — Sat, 06 Jul 2024 03:20:29 GMT

I used Apex Refactor to format the SQL.

SELECT [Number_of_Req] = COUNT([Req].[Number_of_Req]) FROM [Req] INNER JOIN [Date1] ON [Req].[Date1ID] = [Date1].[ID] INNER JOIN [Date2] ON [Req].[Date2ID] = [Date2].[ID] INNER JOIN [Time1] ON [Req].[Time1ID] = [Time1].[ID] INNER JOIN [Time2] ON [Req].[Time2ID] = [Time2].[ID] LEFT JOIN [Date3] ON [Req].[Date3ID] = [Date3].[ID] INNER JOIN [Date4] ON [Req].[Date4ID] = [Date4].[ID] LEFT JOIN [Time3] ON [Req].[Time3ID] = [Time3].[ID] INNER JOIN [Time4] ON [Req].[Time4ID] = [Time4].[ID] WHERE ( [Date1].[ADate] < (SELECT MAX([Date2].[BDate]) FROM [Date2]) OR ( [Date1].[ADate] = (SELECT MAX([Date2].[BDate]) FROM [Date2]) AND [Time1].[ATime] <= (SELECT MAX([Time2].[BTime]) FROM [Time2] ) ) ) AND ( (CASE WHEN [Date3].[CDate] IS NULL THEN [Date4].[DDate] ELSE [Date3].[CDate] END > (SELECT MIN([Date4].[DDate]) FROM [Date4] ) ) OR ( CASE WHEN [Date3].[CDate] IS NULL THEN [Date4].[DDate] ELSE [Date3].[CDate] END = (SELECT MIN([Date4].[DDate]) FROM [Date4] ) AND ( CASE WHEN [Time3].[CTime] IS NULL THEN [Time4].[DTime] ELSE [Time3].[CTime] END >= (SELECT MIN([Time4].[DTime]) FROM [Time4] ) OR ( [Time3].[CTime] IS NULL AND [Time4].[DTime] IS NULL ) ) ) );

You could also use a CTE

WITH MaxDate2 AS ( SELECT MAX([BDate]) AS MaxBDate FROM [Date2] ), MaxTime2 AS ( SELECT MAX([BTime]) AS MaxBTime FROM [Time2] ), MinDate4 AS ( SELECT MIN([DDate]) AS MinDDate FROM [Date4] ), MinTime4 AS ( SELECT MIN([DTime]) AS MinDTime FROM [Time4] ) SELECT COUNT([Req].[Number_of_Req]) AS [Number_of_Req] FROM [Req] INNER JOIN [Date1] ON [Req].[Date1ID] = [Date1].[ID] INNER JOIN [Date2] ON [Req].[Date2ID] = [Date2].[ID] INNER JOIN [Time1] ON [Req].[Time1ID] = [Time1].[ID] INNER JOIN [Time2] ON [Req].[Time2ID] = [Time2].[ID] LEFT JOIN [Date3] ON [Req].[Date3ID] = [Date3].[ID] INNER JOIN [Date4] ON [Req].[Date4ID] = [Date4].[ID] LEFT JOIN [Time3] ON [Req].[Time3ID] = [Time3].[ID] INNER JOIN [Time4] ON [Req].[Time4ID] = [Time4].[ID] WHERE ( [Date1].[ADate] < (SELECT MaxBDate FROM MaxDate2) OR ( [Date1].[ADate] = (SELECT MaxBDate FROM MaxDate2) AND [Time1].[ATime] <= (SELECT MaxBTime FROM MaxTime2) ) ) AND ( ( ISNULL([Date3].[CDate], [Date4].[DDate]) > (SELECT MinDDate FROM MinDate4) ) OR ( ISNULL([Date3].[CDate], [Date4].[DDate]) = (SELECT MinDDate FROM MinDate4) AND ( ISNULL([Time3].[CTime], [Time4].[DTime]) >= (SELECT MinDTime FROM MinTime4) OR ( [Time3].[CTime] IS NULL AND [Time4].[DTime] IS NULL ) ) ) );

Can you accept my reply as a solution if it works for you?

topic Re: Convert DAX query to SQL in DAX Commands and Tips

Convert DAX query to SQL

Re: Convert DAX query to SQL