FILTER - Excelerator BI

# FILTER

## Part 2 – Filtering Out BLANKS in a MEDIANX Measure Using DAX Studio

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 […]

## REMOVEFILTERS() in DAX

Recently Microsoft introduced the DAX REMOVEFILTERS() new function. 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 […]

## The FILTER Function in DAX Part 2

Level: Intermediate In my last article, I introduced the basics of the DAX FILTER() function. Hopefully that introductory explanation to FILTER() helped you get your head around what FILTER() is doing under the hood. With that knowledge in place, let me help you take your understanding to the next level. […]

## The FILTER Function in DAX

Level: Intermediate FILTER() is one of the most important functions in the DAX language. It is quite a simple function and yet many people are scared off when they see it. In this article I will explain the basics of using the FILTER() function in DAX.  Make sure you then […]

## Pareto Analysis in Power BI

Level: Intermediate Pareto Analysis is a statistical technique that applies the Pareto Principle to data. This is more commonly known as the 80:20 Rule. The Pareto Principle is based on the presumption that a relatively small number of inputs (20%)  have most impact on the results/output (80%).  The 80:20 rule […]

## Simple Filters and Syntax Sugar in DAX functions

Level: Intermediate There is a concept in the DAX language called “Syntax Sugar”.  Simply put, the developers have created simplified versions of more complex formulas to make it easier for people to learn and use the DAX functions.  Here are a couple of examples. Total Sales Syntax Sugar = SUM(Sales[ExtendedAmount]) […]

## Using Variables to Write Measures

It’s pretty early in the morning here in Sydney Australia (just after 6 am actually).  I just finished a fun YouTube session with Avi Singh where I demonstrated my approach to using variables in DAX to help write more complex formulas.  Avi streamed the session live on YouTube, and it […]

## User Specified Growth on an Investment using Power BI

Level: Intermediate I wrote an article earlier this year that showed how you can take a table of future growth rates (different growth rates each year) and then calculate the future value of an investment using DAX.  I had a follow up question from Steve asking if it is possible […]

## Number of Days between 2 Transactions Using DAX

Level: Intermediate I got my haircut today (pretty spiffy one too, even if I do say so myself).  While I was chatting I asked my hair dresser “on average, how often should I get my hair cut”?  She told me (for men) around 4-6 weeks.  Then I got thinking (as […]

## Time Intelligence Using DAX

Level: Intermediate Last week I presented a webinar with Chuck Sterling (from Microsoft) on the topic of Time Intelligence.  This was a YouTube broadcast event that anyone could join live, however it has also been recorded and can be watched at any time (see below). This article is a follow […]

Scroll to Top