A Fine Slice Of SQL Server
Marco 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 closedReza Rad explains why you should use DAX variables if you’re repeating calculations:
We have to main parts in the expression above: A and B. Each of those is doing a calculation. Now, with the markings above, reading the expression is much simpler. The whole expression means this:
=IF(A>B, A, B)All the above expression is saying is that if A is bigger than B, then return A, otherwise B. Now it is much simpler to read it because we split the repetitive parts into sections. That is what exactly the DAX variable is for.
Readability is not the only benefit, however. Reza has more.
Comments closedTomaz Kastrun reviews the different DAX functions related to time in Power BI:
New functions are coming to DAX (Data Analysis Expression Language) regularly, and just recently, the function QUARTER was added. And this gave me thinking; why not do a standardized DAX, to be added as a “time dimension” to every Power BI, that I create.
Click through for that standardized time dimension.
Comments closedReza Rad wants to compare prior years to the current year, using year-to-date comparisons:
I have previously written about how to calculate the same period last year calculation and compare this year’s values with the last year’s values. However, sometimes, you don’t yet have the full year, especially for the current year. You might want to compare this year’s value with the last year’s value up until the same day but last year. This is what I call same period last year to date. Here in this blog article, I’ll explain how you can do that using DAX in Power BI. To learn more about Power BI, read Power BI from Rookie to Rock Star.
Click through to see how it’s done.
Comments closedCecilia Brusatori gives us a date dimension created with DAX:
We are going to follow the steps to manually create a dimension Date table with basic DAX. First thing, we need to create a new table.
Click through for the demo. There’s a lot more you can include in a date dimension, but this gives you the starting point.
Comments closedMarco Russo takes a look at how fast DISTINCTCOUNT is in DAX:
This article shows how to implement the same DISTINCTCOUNT calculation in two alternative ways, measuring and comparing the performance in different reports. You will see that while DISTINCTCOUNT can be implemented using SUMX / DISTINCT, the DISTINCTCOUNT version is usually better. That is, unless the density of the reports is high and the calculation apply filters to the measures that do not correspond to the grouping granularity of the visualization – as is always the case using time intelligence functions. There are cases where SUMX / DISTINCT can offer better performance, but you have to clarify whether optimizing one report might slow down many others. Measuring performance using DAX Studio is the only way to know what to expect for your model and reports.
Read on for the full test.
Comments closedGilbert Quevauvilliers wants to see the specific values involved in a Power BI filter:
I was working with a customer trying to get them to better understand DAX and one if the things that is difficult to understand is how the filtering works in DAX.
The challenge I have found is that when using filters in a DAX measure I cannot visually see what is happening within the DAX Filter.
Below I will show you how I can see the values in the FILTER
Click through to see how.
Comments closedAlexander Arvidsson shows how we can concatenate columns in Power BI using DAX:
Finally we can tackle the last hurdle – the column that shows both the current number of certifications and the requested goal. Had this been Excel it would have been dead easy – we just create a new cell that concatenates two other cells like this:
Then we copy the formula to all the rows. Easy. But this is not Excel. The “goal” part is simple – that’s just another column. The trick is to count all the other rows in the table with the same key. Let’s add the key column to the table so we see what we’re working with. “CompKey” is the concatenated key we created in the previous blog post. “Number of certs” is a count of the rows in the table, and because of row context it gets evaluated per key.
Read on for the solution.
Comments closedMarco Russo and Alberto Ferrari team up to share a standard for naming temporary columns in DAX:
The formula works just fine, but it violates one of the golden rules of DAX code: you always prefix a column reference with its table name, and you never use the table name when referencing a measure. Therefore, when reading DAX code, [Sales Amt] is a measure reference, whereas ‘Product'[Sales Amt] is a column reference.
Nevertheless, in our DAX example ProdSalesAmt is a column of a temporary table (SalesByProduct) created by the FilteredSalesAmount measure. As such, ProdSalesAmt is a temporary column that does not originate from any column in the model and does not have a table name you can use as a prefix. This situation creates ambiguity in the code: it is not easy to discriminate between a column reference and a measure reference. Therefore, the code is harder to read and more error prone.
Read on for their standard, which is pretty easy to follow.
Comments closed