A Fine Slice Of SQL Server
The Synthelize blog performs a compare-and-contrast:
In this article we will compare the two main ways to manipulate data in Power BI – Power Query and DAX (Data Analysis Expressions). Along the way we will look at the strengths and weaknesses of each approach and give suggestions on how to make the most of each method.
Click through for the comparison. H/T Toby Basalla.
Comments closedMarco Russo and Alberto Ferrari explain how window functions work in DAX:
Window functions like OFFSET and WINDOW return rows from a table based on the current row. For example, OFFSET (-1) returns the previous row. The main question is: how does OFFSET determine the current row? Intuitively, it searches in the row context and in the filter context for values of columns, and it determines the current row. Unfortunately, that intuitive understanding is missing many details. Although it is easy to intuit the current row in simple queries, things are sometimes more complex. To make sense of the current row in a non-trivial scenario, you need to understand apply semantics.
Read on to learn more, especially when there are partition groups (think the PARTITION BY section in a T-SQL window clause).
Marco Russo provides an option:
DAX is not like M when it comes to data manipulation, and it is not supposed to do that. However, if you need something in DAX similar to Table.FromList in M, this blog post is for you.
If you have a list of values in a string in DAX and you want to obtain a table with one row for each item in the list, you can do the following:
Definitely an example of “There’s a better way to do this than to use DAX” but sometimes you’re stuck, I suppose.
Comments closedMarco Russo and Alberto Ferrari share some advice:
In the article Using KEEPFILTERS in DAX, we described how to use KEEPFILTERS, which is a function that preserves the existing filter on columns affected by a new filter applied by CALCULATE or CALCULATETABLE. We suggest reading that article if you are not familiar with KEEPFILTERS. However, we wanted to clarify a rule of thumb you can apply to quickly decide when to use KEEPFILTERS or not in most cases. This will allow you to only invest more time when you are dealing with specific requirements.
Read on for common use cases of KEEPFILTERS, as well as recommendations on when and how you should use the function.
Comments closedMeagan Longoria solves a problem:
Have you ever wanted to filter a visual by selecting a range of values for a measure? You may have found that you cannot populate a slicer with a measure. But you can do this another way.
I have a report that shows project expenses and budgets. I want users to be able to filter the list of project to only those which have expenses within my selected range. I also have 2 other slicers for project budget and percent of budget used, but let’s just focus on the expense amount slicer.
Read on to see how.
Comments closedKurt Buhler performs comparative analytics:
In a previous article about format strings, we showed an example of how format strings can improve visualizations. The visualizations in that article compared the performance of a company’s marketing videos on a streaming platform to the average of all their videos released that year. In this article, we explain how to conduct this analysis yourself in DAX, where you compare series that occur in different periods.
Click through for an explanation, both in video and in blog form.
Comments closedGilbert Quevauvilliers skips the desktop step:
There have been some updates to Power BI with Visual Calculations, and as of the March 2024 release you can now create and edit visual calculations in the Power BI Service.
Here are details from the blog post: Power BI March 2024 Feature Summary | Visual Calculations Update
I then saw an email which made me think, could I then use the visual calculations to create a DAX measure directly in the Power BI Service on an existing Power BI report.
The answer, it turns out, is yes. Read on to see how.
Comments closedMarco Russo and Alberto Ferrari fit the square pegs into the square holes:
Visual calculations, introduced as a preview feature with the February 2024 release of Power BI, aim to simplify the creation of calculations tied to a specific visual. Using visual calculations for simple calculations is straightforward.
However, as soon as developers create more complex calculations, they should understand the technical details of visual calculation implementation. This requires understanding the hierarchical structure of the virtual table, the new visual context, the semantics of ROWS and COLUMNS, the behavior of CALCULATE, and the new visual context modifiers EXPAND and COLLAPSE.
In this first article about visual calculations, we introduce VISUAL SHAPE and the basics of visual calculation implementation, leaving the remaining topics to future articles. A complete whitepaper with a detailed explanation of all these topics will be available soon to SQLBI+ subscribers.
Even without a complete whitepaper, this serves as a useful primer on the topic.
Comments closedTeo Lachev takes us through visual calculations in DAX:
Suppose you need a measure that calculates the difference between the product categories in the order they were sorted in the visual. Implementing this as a regular DAX measure is a challenge. Yet, if we had a way to work with the cells in the visual, we can easily find a way to get this to work. Ideally, this would work similar in Excel, but DAX doesn’t know a think about cell references. However, visual calculations do.
As always, Teo gives us a view of the good, the bad, and the ugly with this feature.
Comments closedMarco Russo and Alberto Ferrari have a public service announcement:
USERELATIONSHIP is a very common and helpful function, used whenever there are multiple relationships between tables and developers need to decide which relationship to use. However, in some scenarios, this common function raises an annoying error:
The UseRelationship() and CrossFilter() functions may not be used when querying ‘Sales’ because it is constrained by row-level security.
As with all the error messages, this requires some understanding and further explanation. Moreover, a workaround is straightforward to find. However, the workaround has some subtle restrictions that need to be well understood.
Read on to learn more.
Comments closed