locked
Convert SQL to DAX RRS feed

  • Question

  • Hi everyone,

    I am looking to convert the following SQL query to DAX:

    SELECT COUNT([HostName])
      FROM (SELECT HistID, [IPAddress], ResultID, BaseDate, HostName,
                ROW_NUMBER() OVER (PARTITION BY HostName ORDER BY HistID DESC) AS RowNumber
            FROM   [PingHistory]) AS a
      WHERE a.RowNumber = 1 AND BaseDate >= DATEADD(Day, -30, getdate()) AND ResultID <> 0
            AND HostName NOT IN (SELECT [HostName]
                                FROM [ActiveAssets].[dbo].PingHistory
                                WHERE ResultID = 0 AND BaseDate >= DATEADD(Day, -30, getdate()))

    The query counts distinct HostNames with a PingResult other than 0 that have been added in the last 30 days and that have not been added with a PingResult of 0 in those 30 days (Each host has an entry per day)

    I currently have the following DAX, but it doesn't give me the proper results. I also have a date slicer that allows me to view results for specific days.

    OffNet30:=IF(CALCULATE(COUNTROWS(DISTINCT('Ping History'[HostName])),
                   FILTER (ALL(Dates[Date]),
                           Dates[Date] <= MAX(Dates[Date]) && Dates[Date] >= MAX(Dates[Date]) - 30),
                   'Ping History'[ResultID] <> 0) = BLANK(),
            0,
            CALCULATE(COUNTROWS(DISTINCT('Ping History'[HostName])),
                   FILTER (ALL(Dates[Date]),
                           Dates[Date] <= MAX(Dates[Date]) && Dates[Date] >= MAX(Dates[Date]) - 30),
                   'Ping History'[ResultID] <> 0))

    Thanks in advance!

    Thursday, May 30, 2019 2:10 PM