Something cool has just been announced for Azure SQL DB: the ability to call a limited number of REST APIs direct from TSQL. The announcement is here:
Read on for sample code, including a way to execute DAX queries from T-SQL.
Comments closedCategory: DAX
Marco Russo and Alberto Ferrari put on their lab coats:
Fusion is a DAX optimization that reduces the number of storage engine queries when the engine detects that multiple calculations can be merged together in a single query. There are two types of fusions: vertical fusion and horizontal fusion.
Vertical fusion occurs when multiple measures – or calculations in general – need to be computed in the same filter context. For example, the following query requires the calculation of two measures: Sales Amount and Margin:
Read on to see how horizontal fusion differs and when it can be most useful.
Comments closedTeo Lachev builds on a prior post:
This blog builds upon my previous “Resolving Tabular Conversion Errors” and applies to Analysis Services in all flavors (Power BI, MD, and Tabular). In the scenario I described in the previous blog, the server at least told us the name of the offending measure in the error description. But sometimes you might not be that lucky. For example, recently I got this error when running a DAX query requesting many measures: “Microsoft OLE DB Provider for Analysis Services.” Hresult: 0x80004005 Description: “MdxScript(Model) (2000, 133) Failed to resolve name ‘SYNTAXERROR’. It is not a valid table, variable, or function name.” All we know is that there is a syntax error in some measure but good luck finding it if you have hundreds of measures in the query and your model. However, the (2000,133) section references the line number and column number in the MDX script (Yeap, MDX even if you use Tabular), so if we can get the script, we might be able to correlate the error.
Read on to see how you can capture that script and get the error. The technique is definitely not intuitive.
Comments closedIf you’re monitoring activity in Power BI, for example using Log Analytics, you’ll know that you can capture the DAX queries generated by your published Power BI reports. How do you make sense of them though? You may want to know which tables, columns or measures are being used by a query – maybe so you can work out which ones aren’t being used and can be deleted. I always thought the only way to do this would be to parse the DAX query, which would be incredibly difficult to do. Yesterday, though, Igor Cotruta pointed out on Twitter that there’s a really easy way to do this that I didn’t know about, using the DISCOVER_CALC_DEPENDENCY DMV. I’ve used this DMV before and blogged about it here, but what was new to me is that you can pass a DAX query into the QUERY restriction (it is all documented here). To see how it works let’s look at a simple example.
This looks pretty cool.
Comments closedGilbert Quevauvilliers hooks us up:
I have seen a few great blog posts with regards to the new DAX function EvaluateAndLog which can be used to show/debug what happens with DAX Measures.
When I tried this out myself one of the challenges I had was where to download DaxDebugOutput, and then how to use it with Power BI Desktop.
In this blog post I will show you how I downloaded, installed, and used DaxDebugOutput application with Power BI Desktop.
Read on to see how the tool works, as well as where you can get it.
Comments closedMarco Russo and Alberto Ferrari do some ranking:
Ranking is one of the most frequent calculations in Power BI reports. Needing to determine the top products, countries, customers and such is extremely common. RANKX offers a powerful and very fast way to produce ranking. Nonetheless, its use takes some understanding.
In this article we introduce the RANKX function and provide a few interesting examples of how it can be used. RANKX is not a complex function to learn. Nonetheless, most newbies find it intimidating because they do not fully grasp its internals. Once they learn exactly how RANKX works, its use becomes really simple.
Click through to learn how it works.
Comments closedMarc Lelijveld looks back on things:
Over the past few days, I attended the Power BI Next Step conference in Lego land – Denmark. During the keynote, Will Thompson – PM on the Power BI team, showed a new DAX function that is available to all of us already, but was very well hidden in the latest builds of Power BI Desktop. This new function, called OFFSET, allows us to do in context comparisons between two values, without writing extremely lengthy and complex DAX.
I gave it a go and in this post I share my first experiences with this new function and how I think this will make our life easier!
This looks a bit like the combination of LAG() and LEAD() in SQL Marc shows off what’s available now and notes what appears to be forthcoming.
Chris Webb does a bit of sleuthing:
The Switch function is often a cause of performance problems in DAX. Last year a number of optimisations were made that fixed the most common issues (and as a result you should ignore older blog posts that you may find on this subject) but some scenarios still remain where performance can be bad; Marco and Alberto wrote a great post recently outlining some of them that I encourage you to read. How do you even know whether your measure is slow because of your use of the Switch function though? Trying to read query plans, as Marco and Alberto do in their post, isn’t usually practical and commenting out branches in a Switch to see if there is a performance change can be very time-consuming. Luckily, the new EvaluateAndLog DAX function can help you diagnose Switch-related performance issues.
Click through to see how.
Comments closedMarco Russo and Alberto Ferrari explain how the ALLSELECTED function works:
There are two ways to describe what ALLSELECTED performs and what its use cases are: a simple way and a complex way. In this article, we focus on a simple description of the function and its main use cases. We also provide details about when and how you can use ALLSELECTED without having to worry about the intricate details. If and when you want to go to the next level, invest the proper amount of time required to study this article: The definitive guide to ALLSELECTED. Be careful, the details are really complex and if you stick to the best practices, you can safely ignore said details. A real DAX guru should know all the details, but in this article we focus more on an introduction to ALLSELECTED.
Because of its inherent complexity, we describe ALLSELECTED by first focusing on a business case where ALLSELECTED is a good fit. We then provide an imperfect – yet useful – description of what ALLSELECTED is performing. And finally we provide the few best practices to follow when using ALLSELECTED. We will not provide the complete description of ALLSELECTED in terms of shadow filter context, not even as an introduction.
Even the simple solution is fairly complex.
Comments closedJeffrey Wang wraps up a series on the EvaluateAndLog function:
This is the fourth and the last article on the EvaluateAndLog function. While DAX developers were excited about the prospect of printing the values of a portion of a DAX expression, many of the early adopters of the function were puzzled by a mysterious message they ran into from time to time: “EvaluateAndLog function is not executed due to optimization.” Another question on people’s mind is “While it’s great to be able to see the values of a sub-expression used in my calculation, is there a way for me to see the values of other expressions which are not needed to calculate the final result?” I will provide answers to both questions in this post.
Click through to understand why you might get this message and what it means.
Comments closed