Meagan 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 closedCategory: DAX
Gilbert 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 closedGilbert Quevauvilliers uses DAX to calculate a straight-line target:
I had a requirement to take the overall target amount and split it for each month. But also, for it to be cumulative for each month going forward.
The Target amount was 600,000 per month
This is what the DAX measure looked like
Click through for the DAX as well as an example image to tie it together.
Comments closedBill Pearson begins a series on financial functions inside DAX:
As a part of this introduction, you’ll have an opportunity to examine how each function can be employed to support business requirements of the sort that your hypothetical colleagues encounter routinely, and, for the most part, accomplish with Microsoft Excel, in meeting regular business requirements. You’ll learn the purpose of each function, and then undertake a practice example with each that demonstrates how it interacts with a small loan data set, via a calculation that you construct. Moreover, you will:
– Examine the syntax involved in exploiting the function.
– Undertake an illustrative example of the use of the function in a practice exercise.
– Review a brief discussion of the results you obtain in the steps of the practice example.
This is a thorough opening article.
Comments closedMany DAX newbies use LASTDATE to search for the last date in a time period. Or they use NEXTDAY to retrieve the day after a given date. Although these functions do what they promise, they are not intended to be used in simple expressions. Instead, they are table functions designed to be used in time intelligence calculations. Using them the wrong way leads to inefficient code. Moreover, using these functions in ways they were not designed for is a telltale sign that the developer still does not grasp certain details of DAX.
In this article, we elaborate on the topic in order to understand what these time intelligence functions do; we also want to understand the reason why it is so easy to confuse them with simple math over dates. We want to elaborate on this topic through examples. Therefore, instead of starting with boring theory, we start by looking at a calculation that – although it works perfectly fine – is inherently wrong.
Read on for that explanation.
Comments closedKasper de Jonge works around a limitation with the Power BI UI and calculation groups:
AS we have seen calculation groups are great :). It offers amazing flexibility and is extremely easy to maintain. But sometimes it doesn’t do what you want due the limitations of the visuals. Let’s say I want to have a visual that shows me the sales of current year and sales Previous year on different axis (let’s say as line).
You would create something like the visual below where you want to use the same measure but apply different calc groups for each measure. But unfortunately, below visual is not as we want it to be.
But Kasper has us covered with a bit of DAX, so check that out.
Comments closedMarco Russo announces a second edition of DAX Patterns:
Great news! Just one year after releasing the second edition of The Definitive Guide to DAX, we just published a new website, a new book, and a new collection of videos: the second edition of DAX Patterns!
DAX Patterns is a collection of patterns in DAX for Power BI, Analysis Services Tabular, and Power Pivot for Excel. The first edition of DAX Patterns dates back to the end of 2014, and it was based on Power Pivot for Excel. Since then, DAX has evolved with many useful features. Most importantly, Power BI hit the market, and the number of users adopting DAX grew at an exponential rate. When we published the first edition of this book, Power BI had not even been announced yet. Today, most DAX users create a Power BI solution. The new edition of DAX Patterns is thus based on the tool you love: Power BI.
The book looks to be quite useful, and you can get an idea if this content is right for you from the DAX Patterns website. What’s crazy is that they’re offering everything in the book on the website for free, but I’d suggest that if you pick up enough good info from the site, give back by buying a copy of the book or videos.
Comments closedMatt Allington continues a series on blanking out:
This article is a follow on from last week. I recommend you go back and read the article first if you missed it, but in summary, I want to write a measure (not a calculated column) that will return the median sales of products while excluding the products with blanks (no sales). As I showed last week, this is relatively easy with a calculated column. Here it is again. Remember writing calculated columns first is a great way to visualise the problem you want to solve. It is not a great way to solve most problems (some yes, most, no).
Read on to see how you can solve the problem using DAX Studio.
Comments closed