Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hxkresl
Helper V
Helper V

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.

Capture.PNG

 

2 REPLIES 2
Eric_Zhang
Employee
Employee

@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.

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-eq...  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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.