dax

DAX has many time intelligence functions that are often redundant, offering different shorter syntaxes for longer more generic functions. However, sometimes

The Sort By Column feature in Power BI causes side effects that are important to know when writing a DAX formula. This article explains these side effects a

Many of us who have used this function have rarely if ever seen the VALUE argument of RANKX function in use. This post explores a very interesting use of this “VALUE” parameter BUT FIRST …LETS RECALL THE BASICS Here is the link to official Microsoft documentation RANKX takes the followin...

I read this blog (and Matt Allington, and Ken Puls) pretty religiously. But PowerPivotPro is probably my favorite because it deals with a lot of real-world situations, not just technical solutions. So, I’m going to continue that theme and talk about a different kind of problem-solving. How many of you build

DAX supports dates starting in 1900 and while teaching DAX I always explain that DAX manages dates like Excel, which is the reason why these limitations exi

The August 2018 release of the Power BI Desktop adds expression-based conditional formatting rules to the conditional formatting options available for table and matrix visuals. As this is a general…

Figure 1 View outside of my home office window May 2nd, 2018 Fairbanks, Alaska. Intro Hello, my name is Matthew Brice and I am guest broadcasting from my home office (via tape delay) in Fairbanks, Ala…

This article describes possible optimization approaches to improve the performance of nested iterators in DAX. One of the possible reasons the execution of

Thanks to two recent Power BI features it now possible to generate a link on the fly using DAX to go to a new report and pass in any filters. (Alsop imagine linking to your favorite SSRS report !) …

Today I was challenged with a problem and did not find an easy solution to it online, so I decided to write this quick post on it. The challenge was to create a measure in Table1 that would sum on Tab…

Got a question today on how we can do KPI’s in the matrix or table with Power BI, just like we can with PowerPivot through the KPI functionality there. This is where the great SVG trick from…

There have been a lot of posts over the years about how to use RANKX to create a measure, but not so many about how to use RANKX to create a measure that ranks a measure. In addition, nothing really definitive on how to aggregate measures. This post is intended to fix that. Essentially, a measure ...

Like many of my blog posts, this post is inspired by some work I was doing with a customer who had a requirement to calculate quartiles of a ranked set of data. So I thought I’d take a few minutes …

Not long after I started my career in FP&A, I became interested in KPI development. It’s a natural progression I think: you get the budgeting, forecasting, and reporting under control, and then you begin to dig a little deeper. When I got to this slightly more advanced understanding of FP&A, I started to see

“Play Write the greatest song formula in the world, or I’ll eat your souls.”-From “Tribute,” by Tenacious DAX A Do-It-Yourself Time Machine You remember the Great Precedence Project? Well, I have shelved it for now, for two reasons: Creating an exhaustive list of all the precedence rules in PowerPivot

Power BI Measures are a great way to calculate aggregations and summaries of your data. This article explains how you can use Boolean columns to add simple filters to your DAX Measures, and so make it…

In this tip, we will define some essential statistics terms about Lowess and demonstrate how to use this in Power BI.

This week I’m doing something a little bit different. I wanted to post a challenge that I think could apply to many business scenarios, and see the different approaches that people might take…

Estimating the cardinality of iterators is an important skill when optimizing DAX code. This article explains how to use this technique, evaluating differen

How often do you think about quality checking your reports? Sounds about as fun as listening to a lecture from Ferris Bueller’s professor, right? Why Test? Quality testing is one of those things that …

In this week’s episode of Insight Quest, Anna, the team BI manager, has assigned two work items to Brett, the BI developer, as part of the current sprint. The first task is to develop and tes…

Earlier this week I posted a challenge for creating a dynamic date filter using DAX. If you want to see the challenge first and see if you can solve it, check out that post first. If you just want …

A common accounting task is to consolidate the financial results of subsidiary companies into combined results of a parent company. A parent company can have one or multiple subsidiaries. Each subsidiary can also have one or multiple subsidiaries beneath it. The relationships between parent companies and subsidiaries

I was recently doing some work for a customer where they wanted to compare their date to the start date of the following week. They also had a requirement to persist the data to the dataset. I thou…

If you have read the book Alberto and I wrote, “The Definitive Guide to DAX” then by looking at the title you might think that this post is about the bo