How can I adapt this calc made in SQL to DAX?
Note: The variables are set by a Slicer in Power BI:
The real problem is in the CASE function, that change the value of a colum called "PercTime", by the date set in the report
------------------------ Code-------------------------------
declare @StartTime time = '00:00'
declare @EndTime time = '23:59'
declare @Date DATE = '2022-07-09'
declare @Unidade VARCHAR(50) = 'Puma I'
declare @MinimumPerc int = 25
SELECT COUNT(Tagname) as QUANTITY, Name as Operador FROM(
SELECT convert(Date, Timestamp) as Date, SUM(Percentage) / PercTime as total, Tagname, PercTime, Name FROM (
SELECT distinct
[Timestamp]
,[Tagname]
,[Percentage]
,[Compound]
,[Area]
,O.Name
,Unidade
,
CASE
WHEN convert(Date,Timestamp) = convert(Date, current_Timestamp) AND (datepart(hour,@EndTime) < Datepart(Hour, Current_Timestamp)) THEN DateDIFF(hour, @StartTime, @EndTime)
WHEN convert(Date,Timestamp) = convert(Date, current_Timestamp) AND (datepart(hour,@EndTime) > Datepart(Hour, Current_Timestamp)) THEN Datepart(Hour, Current_Timestamp)
WHEN (Datepart(minute, @EndTime) = 59) THEN 24
ELSE
DateDIFF(hour, @StartTime, @EndTime)
END as PercTime
FROM [EvoDashboard].[dbo].[v_Manual]as M
Inner join [dbo].[Area] as N ON N.ID = M.Area_ID
Inner join [dbo].[Operator] as O ON O.ID = N.Operator
WHERE (month (Timestamp) = month(@Date)) and (year(Timestamp) = year(@Date))
) A
Group by convert(Date, Timestamp), tagname, PercTime, A.Name
HAVING (SUM(Percentage) / Perctime) > @MinimumPerc
) B
GROUP BY Name
