A Fine Slice Of SQL Server
Chris Webb continues a series on lambda helper functions in Excel:
In the last post in this series I showed how you can use Excel’s new Lambda helper functions to return tables. In this post I’ll show you how you can use them to return a dynamic array of CubeSet functions which can be used to build a histogram and do the kind of ABC-type analysis that can be difficult to do in a regular Power BI report.
Read on to see a pair of examples along these lines.
Comments closedMatt Allington has a video for us:
The table on the left above shows the change in head count in each department, and is to be populated by the manager. But when it comes to reporting, we really need to know the total change in headcount as a number for each year, not just the first year the change occurred (as shown in the table to the right, above).
There are different ways to solve this problem, but I decided to do it using a combination of Power Query and DAX.
Click through for the video solution.
Comments closedMarco Russo and Alberto Ferrari want to see zeroes in specific circumstances:
What makes this specific product interesting is that the product had sales in 2007, no sales in 2008 and it started selling again in 2009. Its behavior is different than the other products. Indeed, for most of these products one can argue that they start to produce sales when they were introduced in the market. Their behavior is quite intuitive: no sales up to a given point in time, then they start selling. We want to highlight this specific product because it shows a gap in sales when it was already present on the market. For other products, we are happy to blank them until their first sale. By doing this, we show gaps when they are real, and we avoid showing non-relevant information, that is products that could not produce sales because they were not even available to sell.
Read on to see how they do this.
Comments closedAlberto Ferrari dives into a DAX operator:
If you like to follow best practices, you can just read this paragraph out of the entire article. If you are using SUMMARIZE to calculate new columns, stop. Seriously, stop doing it. Right now. Open your existing DAX code, search for SUMMARIZE and if you find that you are using SUMMARIZE to compute new columns, add them instead by using ADDCOLUMNS.
At SQLBI we are so strong on this position that we deliberately omitted a part of the detailed description of the behavior of SUMMARIZE in our book. We understand how SUMMARIZE works but we do not want your code to return inaccurate results, just because you use a function without understanding when its result might be different from the result you expected.
Read on as Alberto explains why, as well as the details of SUMMARIZE and how easily you can find yourself in a mess with it.
Matt Allington reviews a paid product:
Tabular Editor is a Power BI Tabular Modelling productivity tool developed by Daniel Otykier. I blogged about Version 2 of the Tabular Editor in this article here. The 3rd edition of Tabular Editor has just been released, and it is a major upgrade from version 2. TE 3 is not free, but in my view, the productivity benefits make it a must have piece of software for anyone that is regularly writing DAX in Power BI Desktop.
Read on for the review.
Comments closedPhil Seamark has an interesting problem:
The requirement was simple enough. Take the following dataset and, for any given day, produce a count of each possible State using the last known State for any given TestID. The dataset contains six unique Test IDs (A through F). At any given point in time, we first want to establish the last State for each TestID. We also want to group this by day and produce a count value for each possible State. Note, a given TestID can have more than one event in a day, and we only care about the last one.
I’m particularly interested in this because I find a lot of merit in the event-based structure in Phil’s input dataset, but it can be tricky going from that to data in a shape the customer likes.
Comments closedMarco Russo and Alberto Ferrari have another good use case for calculation groups:
Because the axis of the visual must show months outside of the slicer selection, we cannot use the usual Date[Calendar Year Month] column. Indeed, if we put the Date[Calendar Year Month] column on the X-axis, the only visible month would be the selected month. It is worth remembering that the selection of the values to show on the axis is independent from the measure. If a slicer is filtering one month, there is no way to show additional months from the same table on either the rows or the columns (or the X-axis, as in the example).
Therefore, we must create a separate table that is not subject to filtering from the slicer. This way, columns from that table show all the rows, and we can control their visibility through a measure. Once the new table is in place, we write a measure that produces a value for only the last six months out of all the months visible, and leaves the remaining months blank in order to hide them.
Read on to see how.
Comments closedAlberto Ferrari shows how to calculate a rolling 12-month average in DAX:
The measure we want to compute is Rolling Avg 12M, which computes the rolling average of the Sales Amount measure over the last 12 months. When you project the rolling average on a chart, the resulting line is much smoother; it removes the spikes and drops that would make it difficult to recognize a trend in sales.
Click through to see two ways to do this: via a DAX measure and then as a calculation group.
Comments closedMarco Russo and Alberto Ferrari take a look at some new syntax:
DAX now supports expressions where multiple columns belonging to the same table are part of the predicate expression in a CALCULATE filter argument.
Thus, the following Big Sales Amount Overrides Filter measure is now a valid DAX expression:
This is new as of March 2021, so check out the demo-filled example.
Comments closedMarco Russo and Alberto Ferrari talk about case sensitivity:
Every new language defines its own rules of case-sensitivity. R and Python are case-sensitive, DAX is not. It is not that one is right and the others are not; it is really a matter of personal taste of the author of the language. We would say that there is an equal number of pros and cons in both choices. Therefore, there is no definitive choice. That said, a choice needs to be made on two aspects: the language itself and the way it considers strings. Pascal, for example, is case-insensitive as a language, but string comparison is case-sensitive. The M language, in Power Query, is case-sensitive despite living in the same environment as DAX. DAX is case-insensitive as a formula language.
Maybe it’s because I like living in the SQL world so much, but I highly prefer case-insensitivity as the default and case-sensitivity only when necessary.
Comments closed