Forum Discussion

MarceloLeiteJ's avatar
MarceloLeiteJ
Regular Visitor
3 years ago

SQL to DAX

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