Forum Discussion

hxkresl's avatar
hxkresl
Advocate III
9 years ago

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 suggesti...
General Comment
Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
9 years ago

hxkresl

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.

hxkresl's avatar
hxkresl
Advocate III
9 years ago

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.