Forum Discussion
SQL OUTER APPLY - DAX equivalent needed.
I believe Power Query doesn't support OUTER APPLY and my requirement can't be solved any other way.
Is anyone willing to look at the issue and confirm so that I know whether to post as suggestion in later PBI releases?
I have fully built out a demo of my use of OUTER APPLY.
My need of OUTER APPLY has come up a couple times in last 3 years and I see a need for it whenever needing to answer ON/OFF trends for issues that go on for several days running. Answers questions like: Was a backup running during this hour and for how many minutes? Was there an outage during this hour? can not be created any other way but to pair the original dataset with a time table at Min or Hour grain.
pbix https://www.dropbox.com/s/eetbedmowivujib/OUTERAPPLY.pbix?dl=0
DDL https://www.dropbox.com/s/reb977pjq0epffp/DDL.sql?dl=0
end result is a nice left to right scroll of trends. In other cases it can be a heat map with conditional formatting.
2 Replies
I'm not quite sure what you'd like. Do you want an equivalent result of the OUT APPLY SQL query in your pbix file? I mean you'd like to remove the OUT APPLY from the SQL query and want to implement the OUT APPLY in Power Query?
If so, you may not need Power Query. You can import all tables and create proper relationship and implement the OUT APPLY with DAX measures.
Please post more clarification.
By the way, the time table involved in the OUT APPLY query is not included in the DDL uploaded.
Hi Eric_Zhang
I need to pair every row in the source (left table having columns AlertTitle, IssueStartTime, ResolutionTime, ApplicationName) with a row in a Time table (having one column: Time and one row for each hour or minute of the day depending on the granularity desired). There is no join column, there is just pairing going on. The effect outer apply has is to pair every alert from the source with every hour/or minute of a day in the Time table, and then evaluate if the value in the Time.time column, occurs between the issuestarttime and resolutiontime of the alert and if it does, using a case statement, assign the value '1' to HoursDown. This balloons the dataset, but is the only way for me to attach a value of 1 or 0 to the question: was this application experiencing at outage at this hour of the day? Only then can I create a visual such as I have in the DDL i have supplied here.
I have explained in some time ago on this forum and then I took down the post. I also posted on Technet Power Bi. Link is here.
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2b283cf1-2ccf-49b1-b620-cfba7642e930/dax-equiv-historical-trending-of-application-status-solved-in-sql-w-outer-apply?forum=sqlkjpowerpivotforexcel#f3ab8879-e90e-4bfe-8755-d249e73c8d7c the ddl on the technet forum is outdated, so only use for explanation.
DDL (simulated) for source tables is up on my dropbox https://www.dropbox.com/s/eetbedmowivujib/OUTERAPPLY.pbix?dl=0
The query itself is in the pbix, used as basis for OutageTrending reporting table.
