Press "Enter" to skip to content

Category: DAX

Power BI Column Concatenation

Alexander 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.

Leave a Comment

Naming Temporary Columns in DAX

Marco 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.

Leave a Comment

REMOVEFILTERS() in DAX

Matt Allington takes us through the REMOVEFILTERS() function in DAX:

Recently Microsoft introduced a new function in DAX called REMOVEFILTERS(). This is a very useful and well named function and it does exactly what its name suggests. Its purpose is to act as a table filter parameter inside CALCULATE() as shown in the following example.

Total Sales All Products REMOVEFILTERS() = CALCULATE([Total Sales],REMOVEFILTERS(Products))

Read on to see how this compares to the prior/alternative solution and for more information on REMOVEFILTERS().

Leave a Comment

Divide, RankX, and N/A

Rob Collie has some fun with DIVIDE():

A blank cell in a report is sometimes a source of confusion for those human beings consuming our work. “What does a blank cell mean,” they ask.  “It’s a division by zero,” we reply.  “Wut,” they then ask.  “Trust me,” we say, “you don’t want to see the alternative.”  “But I don’t trust you, and now I don’t trust this whole report,” is what they sometimes say next – whether under their breath or out loud.

But “N/A” is a lovely value to display.  It raises far fewer eyebrows.  “Oh, it says our Profit Margin % for electric blankets sold in Cancun is “N/A” – I get it, we’ve never sold that product there.”  No convo required.

Click through for the full story.

Comments closed

Modeling Semi-Additive Measures

Paul Poco shows a couple techniques for modeling semi-additive measures in Analysis Services and Power BI:

As mentioned earlier, the most commonly encountered approach is Option 2, the snapshot fact table. The main drawback of this approach is that the fact table’s size will grow extremely fast. For example, if you want to calculate the headcount in a company with 10,000 employees on average, and you want 5 years of historical data, you will add 10,000 rows per day to your fact table – that gives you (10,000 * 365 * 5 =) 18,250,000 rows after 5 years.  

If you used the first approach, Option 1, the fact table would be (10,000 * 5 =) 50,000 rows after 5 years, assuming your employees change position or quit the company once a year, on average. 

The snapshot fact table (Option 2) is (18,250,000 / 50,000 =) 365 times bigger. On the bright side, as the data is very repetitive, you might get a very good compression ratio on these tables.  

Check it out. Semi-additive measures are not as common as additive measures, but you’re liable to have a couple of them in your data model.

Comments closed

Prior Year to a Specific Date in DAX

Alberto Ferrari lets us compare up to specific dates between years:

Unfortunately, the calculation is not perfect. At the year level, it compares the full previous year against an incomplete current year – in this example there are no sales after September 5th in the current year.

Besides, the problem appears not only at the year level, but also at the month level. Indeed, in September the Previous Year measure returns sales for the entire month of September in the previous year. The comparison is unfair, as there are only five days’ worth of sales in September of the current year.

Read on for a better technique.

Comments closed

Asymmetric Crosstabs in Power BI

Teo Lachev shows how we can implement asymmetric crosstabs in Power BI:

The Internet column shows the sales amount from FactInternetSales. Then, the matrix pivots on the BusinessType column in the FactResellerSales. Because, Internet sales don’t relate to BusinessType, it doesn’t make sense to pivot it. Instead, we want to show Internet sales in a single static column before the crosstab portion starts.

Implementing such a report in SSRS is easy thanks to its support of adjacent groups and static columns but not so much in Power BI. The issue is that Matrix would happily pivot both measures and the InternetSalesAmount would be repeated for each business type.

The solution isn’t awful, but it does involve knowledge of DAX.

Comments closed

Blank Rows and DAX

Alberto Ferrari explains how different DAX functions treat blank rows differently:

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.

Comments closed

Rounding to Intervals in Various Languages

Dave Mason doesn’t like rounding to intervals very much in T-SQL:

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 DATETIME2(7).

Comments closed

Extended Filtering in DAX

Matt Allington continues a discussion on the FILTER() function in DAX:

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.

Comments closed