dax

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

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!

Technical articles, content and resources for IT Professionals working in Microsoft technologies

Back to Part 1 Download this workbook Here The Formula for the Slope of a Linear Regression Line.  It’s Greek to Me.(Get it?  Gree

It’s been almost two years since I posted about the CONCATENATEX() DAX function here. At the end of that post I promised to publish a tutorial on how one might do Market Basket analysi…

One type of analysis I like to use quite often is comparing this year totals to last year totals. I want to also always make it as dynamic as possible. All I should have to do is change the context of my calculation (ie. bring in a new dimension like regions/products etc) and everything should autom...