A Fine Slice Of SQL Server
Marco Russo takes us through the evolution of a language:
Microsoft released 4 new DAX functions, and added 49 financial functions to DAX which correspond to identical Excel functions:
Read on for the summary of those changes, as well as additional important events for DAX and some of the stuff that Marco and Alberto have been working on throughout the year.
Comments closedStephanie Bruno embraces the healing power of AND:
When using slicers in Power BI reports, multiple selections filter data with OR logic. For example, if you have a slicer with products and your visuals are displaying total number of invoices, then when “bicycles” and “helmets” are selected in the products slicer your visual will show the number of invoices that include bicycles OR helmets. But what if you need to have it instead only show the number of invoices that include bicycles AND helmets? Read on to find out how you can do just that with DAX.
Read on for the solution.
Comments closedAlberto Ferrari walks us through ranking based on multiple columns:
DAX offers the RANKX function to compute ranking over a table, based on measures or columns. One limitation of RANKX is that it is only capable of ranking using a single expression. Oftentimes it is necessary to use multiple columns to obtain a ranking, either because the business requirement dictates it, or because you want to rank ties with different criteria.
As a demonstration, we rank customers based on their purchase volume. To artificially introduce ties, we use the Rounded Sales measure, that rounds the sales amount to the nearest multiple of one thousand. Using Rounded Sales, several customers show the same amount of 10,000.00. Because they are ties, their ranking must now be defined by alphabetical order based on their names.
Read on for two methods to solve this problem.
Comments closedPhil Seamark has a new tool for us:
Last week I was honoured to take part in the latest edition of the Power BI Dev Camp which is run by my colleague Ted Patterson. It was a fun session which I enjoyed.
As part of the Dev camp, I walked through some of my recent Visual Studio Code based blog posts on how to perform various tasks against models hosted in Power BI desktop.
While preparing for the session, Ted and I agreed that it might be helpful to create a small external tool that could automatically format all DAX expressions in a Power BI model. The idea is to leverage the excellent DAX Formatter API provided by the good folks at SQLBI. This API is the same endpoint used when you format your DAX using DAX Studio.
Read on for more details.
Comments closedGilbert Quevauvilliers walks us through the Vertipack Analyzer in DAX Studio:
If you are looking to better understand your Power BI Model, how big are your tables, which column is taking up the most space then you can use Vertipaq analyzer which is built directly into the amazing DAX studio.
If you are looking for a video on how to do this, there are some awesome videos found here.
SQLBI.COM – Introducing VertiPaq Analyzer in DAX Studio
Guy In a Cube – How do you even use VertiPaq Analyzer with Power BI???
The reason for my blog post, is that I find sometimes I want to watch a video to better understand the content, and other times I want to follow a step by step process on how complete the analysis.
Click through for a walkthrough of the process.
Comments closedMagda walks us through a function new to Microsoft Excel 365:
Microsoft Excel 365 gives us a new function – LET(). It is not available for every Microsoft 365 subscriber though. A few weeks ago my Excel did not have this function, but now I can work with it. I did a few examples when I was learning this feature and want to share them with you today.
Click through for those examples.
Comments closedMeagan Longoria shows us how Blank works:
Before you read the below tweet, see how many of these you can guess correctly:
Blank + 5 = ?
Blank * 5 = ?
5 / Blank = ?
0 / Blank = ?
Wat.
Comments closedGilbert Quevauvilliers shows off an interesting way of using a Power BI feature:
Recently I had to get some data from a Power BI Dataset. At first, I started writing the DAX using the fantastic DAX Studio.
Then a thought occurred to me, what if I could get the DAX already written and change it to my requirement. This would save me a lot of time and effort. I love the quote from Patrick in Guy in a Cube “I am not lazy, I am efficient”
Click through for the scenario. Looks like it will get you at least part of the way there.
Comments closedEach product in Contoso weighs a certain weight. The weight is stored in two columns: the unit of measure and the actual weight, expressed in that unit of measure. Specifically, Contoso uses three units of measure: ounces, pounds, and grams.
Because the units of measure are different, you cannot aggregate the weight over different products. If you author a simple measure that computes the ordered weight of products by using a simple SUMX, the result is wrong:
Click through to see how you can work through this problem.
Comments closedMarco Russo and Alberto Ferrari walk us through three important set functions in DAX:
In this article we refer to “set functions” as functions that operate on sets. The three set functions available in DAX are: UNION, INTERSECT, and EXCEPT. Their behavior is very intuitive:
– UNION performs the union of two or more tables.
– INTERSECT performs the set intersection between two tables.
– EXCEPT removes the rows of the second argument from the first one.These functions take two or more tables as parameters and return a table. They prove useful not only to write DAX queries; a developer can also use these functions to prepare complex filters when implementing measures.
Read on to see how these work in DAX.
Comments closed