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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
hxkresl
Helper V
Helper V

SQL OUTER APPLY - DAX equivalent?

I will need to convert a SQL statement that takes a base dataset and OUTER APPLYs it to a Date table, to cause the right-side query (Date) to execute once per result in the left-side query (base dataset).   The reason I will need this is to allocate a portion of the time between issuestartdate and resolutiondate of every Alert raised for application outage to it's own row, so that I can flag the application as either up/down at the minute level.  This I will use to build a historical trending with line chart/or bar graph visualization that shows what portion of a day (1440 minutes) an application was up and when it was down.

 

Is OUTER APPLY supported?

8 REPLIES 8
vanessafvg
Super User
Super User

@hxkresl

 

also there is this guided learning to help you get going

 

https://powerbi.microsoft.com/en-us/guided-learning/





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




i will review the articles and thanks for the guidance.  I think you are saying to use power query for broad sweeps at shaping the data.  Perhaps that is what i need to think about as well.  At any rate I will review and come back with DDL with actual SQL if I don't make headway.

@vanessafvg

I read the material links. Doesn't look like OUTER APPLY is supported....worried.

Would you mind looking at the problem in http://community.powerbi.com/t5/Developer/DAX-equiv-needed-Historical-Trending-of-application-outage...  

different issue but with same dataset is here:

 http://community.powerbi.com/t5/Desktop/Need-date-sorting-on-bar-graph/m-p/187584#M82502 

Can I do this historical trending with DAX?  Is there an alternative to OUTER APPLY? Or just forget SQL, can this be done in DAX?

vanessafvg
Super User
Super User

@hxkresl

in dax relationships you can  create a variety of relationships

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

 

as well as power query there are a variety of join methods

 

http://radacad.com/how-to-change-joining-types-in-power-bi-and-power-query





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




@vanessafvg

yes, i googled this question before posting.   both articles are two years old, so uncertainty remains about outer apply.  

@vanessafvg  can you show example of OUTER APPLY in DAX (not power query)please?  I simply don't know how to start it and am looking for tutorial.

@hxkresl 

 

there is this

 

https://msdn.microsoft.com/en-us/library/dn802527.aspx

 

but ive never used it before and im not sure it does outer apply, personally i have not used any join type syntax in dax the way i would sql.    

 

being a sql person for 20+ years i can say that there is a mind shift involved in engaging with DAX so sometimes trying to apply the same logic will have you chasing your own tail.  Power Query is also there to make the shaping of data really easy so try it too if it makes sense.  Get the data into the shape you want it and then using dax to do more calculations than transformations is probably the best way to go.

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




@hxkresl have you attempted anything?   outer apply being an outer join.  in power query the article see below

 

even if the articles are 2 years old the technology hasn't changed that drastically, ie. im unclear what you are looking for?  why dont you post your sql statement

 

from the power query article

 

Capture.PNG





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.