Category: DAX

Phil Seamark continues a discussion on single-column fusion:

You may notice the [Package – Bag] measure uses the * (multiplication) operator in line 4 between a measure and what looks like a column filter.

The [base measure] performs a simple COUNTROWS aggregation and returns an INTEGER. This side of the equation makes sense, so let’s see what is going on with the DAX on the right-hand side of the * operator? The filter statement is encompassed with parenthesis, which automatically converts the expression to a TRUE/FALSE boolean value – which is then implicitly converted to an INTEGER value of either 0 or 1. It can’t be anything else.

The result of this double conversion is that we end up with a INTEGER * INTEGER to produce the number we see in the visual.

Click through for plenty more where that came from.

Kristyna Hughes explains the concept of dynamic measures in DAX:

For example, you may have three major stakeholders looking at a report: CEO, CFO, and COO. The CEO may be more interested in future profits and looks at trends in the number of loads booked to see where we are headed. The CFO wants to see profit trends on loads that have already been completed (actualized profit). Additionally, the COO would rather see what’s currently in transit and look at only picked up loads. You could make three pages, one based on booked loads for the CEO, another based on delivered loads for the CFO, and one based on picked up loads for the COO. But what happens if your report is already going to be multiple pages and needs room to grow? You would need to triple your page count to accommodate the needs of your users or build three completely separate reports! Don’t worry, dynamic measures can solve this problem for you without clogging up your reports or workspaces with extra measures, visuals, and reports.

Click through for the demonstration on how to combine this with a slicer to change reported measures.

Nick Edwards shows how you can use the ConcatenateX DAX function to combine values:

In this blog post we’ll take a quick look at using ConcatenateX function to view a concatenated string of dates where the max daily sales occurred for a given month.

I came across this function whilst going through the excellent “Mastering DAX 2^nd Edition Video Course” by the guys from SQLBI.com. So credit to Marco and Alberto for sharing this.

So how does it work? If we had a list of dates ranging from 01/01/2020 to 31/12/2020 and we wanted to see which days we achieved maximum sales for each given month in a year we could use the ConcatenateX function to return these dates in a single row per month.

Click through for the demo.

Matt Allington points out a new look to a slightly less new feature:

When I say “new”, they are actually not new – this feature has actually been around since the start of 2019. What is “new” is the discoverability of the feature. Prior to the April 2020 release, you had to first hover your mouse button over the section (Title text in the example above). After you hovered your mouse, you would see a vertical … menu (kind of like a vertical ellipsis), then when you hovered over the vertical ellipsis, you would then see the Fx button. Click the mysterious hidden button and only then could you discover the world of expression based formatting.

Click through to learn about expression-based formatting and where it might be useful.

Reza Rad takes us through the GroupBy function in DAX:

There are many different ways you can create aggregations in Power BI, You can do it in the source (using the database t-SQL language), or using Group By operation in Power Query. You can also do it in DAX using some functions. One of the functions that can be used for grouping and aggregation is Group By. This article is about how to use Group By in DAX. Creating aggregation using DAX is a very useful skill because you can use it to create virtual tables in your measures and have better dynamic calculations in Power BI.

Click through for the explanation and an example.

Phil Seamark explains what the KEEPFILTERS() function does in DAX:

If you ever spend time looking at DAX generated by the Power BI Desktop Performance Analyser, you may notice a function called KEEPFILTERS appear from time to time. It’s not a function I find myself using often, so I thought I’d do a bit of digging to find out what it does and when it might be useful.

Read on to learn exactly what it does.