A Fine Slice Of SQL Server
Phil Seamark explains what the KEEPFILTERS() function does in DAX:
If you ever spend time looking at DAX generated by the Power BI Desktop Performance Analyser, you may notice a function called KEEPFILTERS appear from time to time. It’s not a function I find myself using often, so I thought I’d do a bit of digging to find out what it does and when it might be useful.
Read on to learn exactly what it does.
Comments closedReza Rad takes us through the subscription pattern:
One of the common patterns of a data model is the subscription pattern. In this pattern, we have subscriptions (or tickets, or issues, or whatever else you want to call it) open date and close date. The way that this data is stored in a table makes it a bit challenging to get informative insight out of it. In this post (first of the series), I am going to explain about the subscription pattern and one of the common calculations needed for it; which is active subscribers at any given date, or open tickets at any given date.
In case you’re curious, here’s a solution which works in T-SQL. I’ve really taken to event-style tables, where there’s one row per state change, so instead of having a begin date and an end date for each action, have a row which contains the date and the type of action. This makes operating on the data a lot easier, though it does make rules preventing common entry problems (end date before start date, etc.) a bit trickier.
Comments closedMarco Russo and Alberto Ferrari take us through the COALESCE() function in DAX:
COALESCE accepts multiple arguments and returns the first argument that is not blank. If the first argument is blank, COALESCE returns the value provided by the expression in the second argument, and so on. If all the arguments are blank, COALESCE returns blank as well.
Good for SQL users, who should already be familiar with the function.
Comments closedI am currently running SQL Server Analysis Services (SSAS) 2019 Enterprise Edition. (This can also be applied to Power BI)
My Fact table has got roughly 950 Million rows stored in
And as mentioned previously it has got over 64 Million distinct users.
The data is queried from SQL Server into SSAS.
Gilbert first checks how close these are and then how much faster the approximate count is.
Comments closedKoen Verbeeck takes us through some unexpected results with the SUMX() function:
Hold on, the result is different for the 29th than the 28th? This is what the title means with incorrect (or unexpected) results. Even though the data looks exactly the same in the table, the result are not.
It is not a bug in the DAX formula language. The problem resides with the duplicates in the table.
Read on to understand what this means and how you can change your code to prevent this issue.
Comments closedErik Svensen shows us what happens in DAX functions when February 29th rolls around:
Yesterday I visited a client and was asked – how do the time intelligence functions handle the fact that February has 29 days in 2020.
Well – in fact there was a few surprises depending on what you select from you date table.
Let’s look as some examples – I will use the following Internet Sales Amount from the years 2011-2013 from Adventure Work Database where we in February 2012 have 29 days.
Click through for those surprises. They aren’t bugs, as the functions have to do something and the possible set of valid somethings is greater than one.
Comments closedMarco Russo and Alberto Ferrari want you to watch your money grow:
Coincidentally, both debt instrument examples are what is known as “bullet” loans, where the entire principal amount ($100) is repaid in one lump sum at maturity (at the end of Year 5). In the first example the interest income payments are deferred until maturity, thereby allowing the interest to compound over the holding period. In the second example, the interest income payments are made at the end of each year, which means that the amount of debt accruing interest each year is always the same ($100).
Now let us consider a slightly more complex investment with compounding interest where the interest rate differs year-to-year. Because the interest rate varies, you can’t use the simple formula above (or its FV function equivalent in Excel). Rather, you must effectively stack each year on top of the preceding year and calculate year-by-year.
And that’s something you can do with DAX.
Comments closedShabnam Watson answers a couple of questions around aggregations and time series in Power BI:
I have received a couple of questions about Aggregations in Power BI and whether they can be used to cover time series calculations such as Year to Date, Quarter to Date, and Month To Date. The answer is yes. Since time series calculations break down into calculations over a series of days, an aggregation table defined at day level with the basic summarization methods (min, max, sum, count) and the right relationship with a Date dimension, can answer Year to Date, Quarter to Date, and Month To Date calculations.
Let’s take a quick look at one such calcualtion and how it can be covered with an aggration. I am going to use the same version of AdventureWorks sample database and Power BI model that I used in my previous blog post on aggregations, with a few changes.
Read on for a demonstration.
Comments closedMarco Russo shows us what’s new with DAX in 2019:
Microsoft released 13 new functions in 2019. The first 4 functions are related to the calculation group feature, which is now only available on Azure Analysis Services and Analysis Services 2019:
Read on for those 13 functions. Then, keep reading to see what Marco, Alberto, & crew have in mind for 2020.
Comments closedMarco Russo shows us how to build the equivalents of NULLIF and COALESCE in DAX:
The COALESCE function in ANSI SQL returns the current value of the first expression that does not evaluate to NULL. The syntax is:
COALESCE( <expression1>, <expression2>, <expression3>, … )We can rewrite this using IF in DAX this way:
Also read the big disclaimer at the top.
Comments closed