I need help in translating the following SQL to DAX

[ranjanrkl]

SQL 1:

CONVERT(VARCHAR(8),CASE WHEN
(SUM(CASE WHEN InResult IN ('I2','I5','I8','I9','I18','I25','I31','I32','I33') AND
FinalFate IN ('Q10','Q24','Q26') AND
DATEDIFF (s, RouteToQTime, AgentAnswerTime) > 0
THEN DATEDIFF(s, RouteToQTime, AgentAnswerTime)
ELSE NULL
END)) IS NULL OR
(SUM(CASE
WHEN InResult IN ('I2','I5','I8','I9','I18','I25','I31','I32','I33') AND
FinalFate IN ('Q10','Q24','Q26')
THEN 1
ELSE NULL
END)) IS NULL
THEN 0
ELSE DATEADD(ss,SUM(CASE WHEN
InResult IN ('I2','I5','I8','I9','I18','I25','I31','I32','I33') AND
FinalFate IN ('Q10','Q24','Q26') AND
DATEDIFF (s, RouteToQTime, AgentAnswerTime) > 0
THEN DATEDIFF(s, RouteToQTime, AgentAnswerTime)
END)/SUM(CASE
WHEN InResult IN ('I2','I5','I8','I9','I18','I25','I31','I32','I33') AND
FinalFate IN ('Q10','Q24','Q26')
THEN 1
END),0)
END,108) AS [RC HoldTime]

SQL 2:
SELECT
CASE WHEN (GROUPING(QueueName)=1)
THEN 'Grand Total'
ELSE QueueName
END AS Queue,

Please note, we are on SQL Server 2016 (SP3) (KB5003279) and IN Operator in DAX is not supported in SQL Server 2016.

Thanks in advance.

[v-yiruan-msft]

Hi @ranjanrkl ，

In order to give you a quicker solution, could you please share the example table data referenced in these two SQL query tables (with sensitive information removed) and the final result you want. Also, please explain the calculation logic in detail.

Also, in the above two SQL statements, the CASE... WHEN statements can be implemented with DAX IF or SWITCH function, and IN can be implemented with DAX IN function. For details, you can check the links below.

Write Conditional Statement Using SWITCH in DAX and Power BI

The IN operator in DAX

In addition, you can connect to the SQL Server database in Power BI Desktiop and fill in the two SQL Query in the Advanced opitions below. The only problem is that you will only get one table...

Import data from a database using native database query

Best Regards