DAX offers two functions to retrieve the list of values of a column: VALUES and DISTINCT. The difference between the two is subtle. To understand it better, we first need to introduce the concept of the blank row. The blank row is a special row added to a table, in case the table is on the one-side of a strong relationship and the relationship is invalid. Let us elaborate on this.
Click through for the detailed explanation, along with plenty of examples.
If I had to choose any of these options for production, I’d probably go with Query 1, just because I think it would make the most sense to any other developer that might encounter it (including future me, who probably wouldn’t remember writing the code). But I’m not really thrilled with any of the options. The one thing T-SQL has going for it though, is the relative ease for truncating the TIME off of a DATETIME by casting to DATE and back to DATETIME. I couldn’t find anything like this in DAX.
Dave also shows how to do this in DAX and Powershell.
Another alternative that Dave doesn’t mention is to invert the problem: if you have a fixed set of intervals you care about (e.g., 15-minute, 30-minute, hour, 4-hour, etc.), you can create a time table. This is like a date table but contains times of the day where you’ve precalculated the intervals. Then you join to the time table and have your results right there. If you do go this route, I’d try to keep the grain of the time table as shallow as possible, maybe using
DATETIME2(0) instead of
The new formula follows the rule “don’t filter a table if you can filter a column”. But in this case the column and the table have the same cardinality, so there is little benefit there. Also, the new formula requires a second CALCULATE() and SUM() inside the FILTER() function. This is required because the column Customers[YearlyIncome] is no longer in the same table that FILTER() is iterating. The FILTER() function is iterating a virtual, single column table that contains all customer keys in the customer table. The column Customers[YearlyIncome] doesn’t exist in this virtual table, it exists in the Customers table, so you must wrap the column in an aggregation function, SUM() in this case. Further, as the FILTER() function iterates in a row context through the virtual table, the virtual relationship does not filter the connected tables UNLESS you specifically tell the formula to do so. Technically, to make the filter propagate from the new virtual table created by ALL(Customers[CustomerKey]), we need to convert the row context into an equivalent filter context via context transition. Context transition is triggered by the inner CALCULATE() inside the FILTER() function in this case.
Read on for several tips for efficient filtering.
FILTER() is most often used as a filter parameter within the calculate function. OK, so now it is getting confusing. FILTER() is a function. There is another use of the word filter in DAX, and that is as a parameter in CALCULATE. Let’s look at the syntax
FILTER Syntax: FILTER(<table>, <true/false test>) CALCULATE Syntax: CALCULATE (<measure expression>, <filter parameter>, <filter parameter>,…)
This is one of the most useful functions in DAX and one you really want to know well.
As explained in the blog post the SUMX in this calculation will iterate over each row in the fact table which probably will have multiple currencies with different values for each date. The Min(FactExchangeRate[Factor]) will be evaluated for each currency and date and get the right value.
Now for those of you who have seen any of my sessions will ask why am I not using a variable as I always tell everyone to do so.
Click through for the answer and an example of where you can use a variable within an iterator.
The thing both of those formulas have in common is that they are using a measure in the filter argument of the CALCULATE function. In both examples here, I’ve highlighted the offending measure in yellow.
CALCULATE([Sightings per Year], [Avg Sighting Length in Mins]>6)
CALCULATE([Sightings per Year],
Observations[TTL Min]>[Avg Sighting Length in Mins])
In the first formula, I was trying to use a measure on the left side of the comparison, and in the second, I was trying to use a measure on the right side of the comparison. Both are illegal.
Read on to see why and how you can use
FILTER() to solve these problems.
Have you ever written an array formula in Excel? (Don’t worry, most people haven’t). Have you ever written a FOR loop in a programming language? (Again, don’t worry, there’s another question coming). Have you every repeated something over and over again, slowly building up to a final result?
That’s what SUMX() does. It loops through a list, performs a calc at each step, and then adds up the results of each step. That’s a pretty simple explanation, but it produces some results in pivots that are nothing short of spectacular.
Read on for a few examples.
When you’re dealing with a beast like DAX you can use any help there is, right? So here I show you how you can debug DAX variables who contain tables or show the result of multiple variables at once. So you can easily compare them with each other to spot the reason for problems fast.
Please note, that currently only comma separated DAX code is supported.
Click through for a demo as well as a video.
As of this month Power BI finally supports filtering slicers down to only show rows that have fact data. Before the only thing you could do to achieve this was some workaround like: I described here where you filter down the dimension using a calc table. The other approach was to use Bi Directional cross filtering which would filter down the dimension table appropriately. This leads to performance issues though.
Now you can use a measure to filter down the slicer.
Click through for an example as well as a few other tricks you can do as a result.
At this point, extracting the first row from Results which would contain the string to produce in the report, is sufficient. TOPN is the function extracting that row, but there is a major drawback here: in case of a tie, TOPN returns all values involved.
In case TOPN returns multiple rows, it is necessary to show them one after the other so to make it clear that the result is not unique. CONCATENATEX is an iterator that concatenates strings and produces a single string out of a table.
This is a good demonstration of a useful function.