Power BI

Just a quick one today on how you can use the Power BI quick calcs with you own date table. I have a very simple table with a date column in it: Now I want to show the YTD over time (so crossing mu…

Welcome to a new series that I’m starting with a few colleagues at Pragmatic Works. The goal of this series is to document many design practices and solutions to problems that we often run into wit…

I read a quote somewhere along the lines of… If you are trying to solve a problem with regular expressions… you now have two problems This seems a little harsh for a useful tool that ha…

This article presents different techniques to compute a rownumber column in DAX based on a specific ranking, comparing slow and optimized approaches. In se

How to Master SUMX in DAX. Master SUMX using these simple techniques shown using examples. This makes DAX iterators easy! NFORM +61 8 7228 0515

Did you know that it’s easy to transform Power BI’s Line Chart into a Slopegraph? No DAX or fancy workarounds are required. Slopegraphs are a great way to judge the rate and magnitude o…

Please note the performance is not optimized for a very large dataset, try with your own data. Create a Median Meausre [Lowerbound] =MINX( FILTER( VALUES( BoxOffice[TicketsSold]), CALCULATE( COUNTROWS( BoxOffice ), BoxOffice[TicketsSold] = COUNTROWS( BoxOffice ) / 2 ), BoxOffice[TicketsSold]) [Upperbound]=MINX( FILTER( VALUES( BoxOffice[TicketsSold]), CALCULATE( COUNTROWS( BoxOffice ), BoxOffice[TicketsSold]

When analyzing data, I often find myself computing the percentile value. As I am a big fan of PowerPivot , I went w...

Building from my last post, Performance Metrics: Average, Mean, Median, and Outliers, where I covered the topic of when to use Median instead of Average, in this post I’m going (to try) to sh…

“AGAIN!” -Pai Mei       SUMX() – the great iterator Have you ever written an array formula in Excel?  (Don’t worry

I was helping out in the Power BI Community and I came across the following requirement. The user had sales figures and based on the Previous month, they wanted to have an arrow showing if it was u…

I recently discussed with Darren Gosbell about how to calculate the Median in DAX. The problem is to make the calculation using a measure, so that you can use it in a dynamic way into a PivotTable. We have a People table with three columns: Gender, Customer

DAX Patterns is a collection of ready-to-use data models and formulas in DAX, which is the programming language of PowerPivot. Create your Excel data model faster by using a DAX pattern!

You might have used FIRSTNONBLANK and LASTNONBLANK in semi-additive measures, but you might not be aware that their use is not limited to time intelligence

Last month, I explored how to take a Power BI area chart and make it into a unique “new” chart type using only data and DAX formulas. The line chart is also a candidate for a bit more f…

EARLIER is a DAX function that acts exclusively on row context,  and its purpose is to create a reference to a column value on an outer loop of the evaluation of the expression.  It is commonly use…

“…then they start thinking that, ‘where there’s smoke there’s fire’ logic…” Continuing the Impromptu Series of Simple Real World Examples The Mr. Ex

Egg on My Face For Improper Use of EARLIER() Tonight I was looking at one of my recent posts, the one about “fuzzy” time relationships in calculated

Sometimes reporting data values day-by-day can create the impression of dramatic ups and downs, when really what we want to see is the general trend of changes over time.  We need, in other words, to create a chart based on moving averages.  Such a measure is commonly used in tracking the price of stocks, but […]

KPI for easy Profit and Loss in PowerBI as the last part of my series: Learn how to write them for columns and rows with minimal efford.

Gaining an understanding of Row and Filter Context is among the most difficult challenges facing a new DAX author. Just when you think you've got it, a problem arises and your hard-won mental model seems to crumble before your eye. For a new author this is particularly tricky in that virtually every...

Jumpstart your DAX development with this helpful cheat sheet.

This article describes different techniques to retrieve multiple values from a lookup table in DAX, improving code readability and performance. There are a