A Fine Slice Of SQL Server
Marco Russo and Alberto Ferrari don’t want to let any information slip out:
Let us start with a simple challenge: we want to show the percentage of sales in Europe, compared to the sales made to all customers worldwide. It is a relatively trivial question, the kind of DAX code you learn at the beginning of your Power BI career, and it can be solved with a simple measure:
But what happens when the model includes security roles? Well, that’s what you’ll have to read on to learn.
Comments closedAllison Kennedy checks the calendar:
Time Intelligence functions in DAX change the filter context on the Date table of your model.
Step 0: Prepare
Before using Time Intelligence functions, you should:
- Check out the documentation at https://dax.guide/functions/time-intelligence/
- Make sure your model has a Date table that’s marked as such: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
- Have a basic understanding of the CALCULATE function – it’s crucial to this solution
Read on for a four-step process covering how to apply a time intelligence function like DATEADD() or DATESYTD() in DAX.
Olivier Van Steenlandt covers a common case:
Many companies don’t follow the regular Calendar as we know (January 1st – December 31st). They follow their own Financial Calendar (often called Fiscal Calendar) which can start at any time of the year.
Because of this, writing Year-To-Date calculations in DAX for your Tabular Model might seem challenging.
In the step-by-step example, we are working for a company that starts its Financial Year on July 1st.
Read on to see one way to do it. It doesn’t quite solve the problem Olivier brought up, but I’d also make note that having a calendar table with fiscal + calendar year information in it helps remarkably well. It can even handle multiple fiscal year concepts; as an example, a state agency I worked for had a fiscal year on July 1 but the US federal government’s fiscal year begins October 1, so it was just a matter of having StateFiscalYear and FederalFiscalYear columns.
Also, check out Olivier’s new theming, under the Data Cuisine motif.
1 CommentSandeep Pawar makes the code a bit more readable:
There is an old Italian saying “If it’s not formatted, it is not DAX”
When you get the list of measures from SemPy, it’s not formatted and is hard to read and understand. Thankfully, the SQLBI team has made the DAX parser and the formatter available via an API. I wrote a quick function to return the formatted DAX expression of a measure. You can either pass a DAX expression or the FabricDataFrame returned by
fabric.list_measures()
Click through for the process, including the Python code to do the work.
Comments closedMarco Russo and Alberto Ferrari speed things up a bit:
Pushing calculations down to the VertiPaq storage engine is always a good practice. Sometimes this is not feasible. However, carefully analyzing the aggregated expression can lead to optimization ideas that produce excellent query plans.
DAX developers should not be scared of iterators. Their performance is great as long as the expression computed during the iteration can be pushed down to the VertiPaq storage engine.
Read on to understand what they mean by callback and the enormous performance cost you’ll want to avoid.
Comments closedMarco Russo and Alberto Ferrari perform some formatting:
If you want to build a report where the user can choose what measure to show, you have two features available in Power BI: field parameters and calculation groups. There are pros and cons to either technique – however, we are not about to talk about those. We narrow our scenario down to a specific requirement: we want to change the color of the value depending on the measure selected.
For example, suppose we let users choose between Sales Amount, Margin, or Total Cost. In that case, we might provide visual feedback about the measure selected through different colors: black for Sales Amount, green for Margin, and red for Total Cost.
Click through for that example, though I will say that the color choices are hard to differentiate if you have protanopia and even more difficult if you have deuteranopia, so about 2% of the male population would struggle with interpreting this measure. People with protanomaly and deuteranomaly (about 6% of men) wouldn’t have too much difficulty with this particular color pairing.
Comments closedNikola Ilic shines a light on the data:
To quickly explain: when a user selects, for example, Contoso in the slicer, the Contoso bar should be highlighted by using a different color. As much as this sounds like a very basic and common business request, there is no straightforward solution in Power BI (or, at least, I’m not aware of it:))
However, the client’s wish is (almost) always our command – so, let’s see how this feature can be implemented with a little bit of data model tweaking and leveraging some DAX code.
Spoilers: there is a solution, though it does involve quite a few steps.
Comments closedMarco Russo and Alberto Ferrari observe the dog that didn’t bark:
What products did not sell in a specific area, store, or time period? This may be an important analysis for several businesses. There are multiple ways to obtain the desired result. Some specific implementations might be needed because of the user or model requirements, whereas developers can choose any formula in several cases. Or you might just find a solution on the web and blindly implement it without questioning whether there is a better way to achieve what you want.
It turns out that different formulas perform very differently. Choosing the right one in your scenario can make a slow report fast. This article analyzes the performance of different formulations of one same algorithm.
It’s interesting to see the performance profile here: most are reasonably close together, although you can still get a 2x gain from using the fastest approach versus the second-slowest. And then there’s the slowpoke.
Comments closedDAX is a powerful tool in the hands of a Power BI developer. Using simple DAX formulas, you can not only compute interesting metrics but also customize the behavior of Power BI visuals. In this article, we use DAX to control the range of charts to obtain more coherent visualizations.
Read on to see how.
Comments closedA few months ago, while scrolling through posts on social media, one of them immediately grabbed my attention! It was about a new tool, called DAX Optimizer, which promised to identify and remove performance bottlenecks in your DAX formulas. For all of us dealing with optimizing Power BI reports on a day-to-day basis, that was a huge promise (and one I was impatiently waiting to see in action).
One important note is that this is not a free tool, as Nikola mentions. Read on for more thoughts about how it works, what it picks up, and whether it’s a good fit for your environment given the price.
Comments closed