Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have the following report query from Solarwinds and would like to replicate it within Power BI.
SELECT Interfaces.InterfaceId,
Nodes.NodeID,
Nodes.Caption AS NodeName,
Nodes.DetailsUrl AS NDetailsUrl,
Nodes.VendorIcon AS Vendor,
Interfaces.Caption AS Interface_Caption,
Interfaces.InterfaceIcon AS Interface_Icon,
Interfaces.DetailsUrl AS IDetailsUrl,
Maxbps_In95,
Maxbps_Out95,
Maxbps_95 FROM Orion.Nodes INNER JOIN Orion.NPM.Interfaces ON Nodes.NodeID = Interfaces.NodeID INNER JOIN ( SELECT OuterInterfaceTraffic.InterfaceID,
(SELECT MAX(InAveragebps) as maxInMaxbps FROM ( SELECT TOP 95 PERCENT InAveragebps FROM Orion.NPM.InterfaceTraffic WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID AND DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE()) ORDER BY InMaxbps ASC )) AS Maxbps_In95,
(SELECT MAX(OutAveragebps) as maxOutMaxbps FROM (SELECT TOP 95 PERCENT OutAveragebps FROM Orion.NPM.InterfaceTraffic WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID AND DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE()) ORDER BY OutMaxbps ASC )) AS Maxbps_Out95,
(SELECT MAX(Maxbps) as maxMaxbps FROM ( SELECT TOP 95 PERCENT Maxbps FROM (SELECT (CASE WHEN OutAveragebps > InAveragebps THEN OutAveragebps ELSE InAveragebps END) AS Maxbps FROM Orion.NPM.InterfaceTraffic WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID AND DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())) AS MaxbpsSet ORDER BY Maxbps ASC )) AS Maxbps_95 FROM Orion.NPM.InterfaceTraffic AS OuterInterfaceTraffic WHERE OuterInterfaceTraffic.DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE()) GROUP BY OuterInterfaceTraffic.InterfaceID ) TrafficStat ON Interfaces.InterfaceID = TrafficStat.InterfaceID
The problem is that DateTrunc is not a recognized built-in function name. Can anyone please help me customize this query to work within Power BI?
Solved! Go to Solution.
I am trying to pull 95th percentile for the previous month.
I am trying to pull 95th percentile for the previous month.
Hi @Anonymous ,
It seems that you want to convert the query to dax in power bi.
You could refer to this article at first.
In addition, you could try MONTH () to replace DateTrunc('month', GETUTCDATE()).
If you still need help, you'd better share some data sample and your desired output so that we could help further on it.
Best Regards,
Cherry