Have you Heard This Joke: Gil, Austin, and Rob (left to right) Walk Into a Bar…(Yes, Rob’s hair was purple at the time.)(Note from Rob: and it’s still purple today!) Intro from Rob Well folks it’s an exciting time to…
Out of 407 Total Combinations of Subcategory and Region, These 8 Stand Out This Month (These 8 Combinations Differed GREATLY from their Respective 12-month Averages) A Post Long in the Thinking: Formulas that “Scan” (What’s this, you say? An actual…
A long time coming… The Finalized 8-Page “Reference Card V2” – Download it for Free Below Yes, this took me forever. I first posted about it more than six months ago. It took a lot of effort, for sure, but…
Director of Product Management, Microsoft BI (At left: the many faces of Kamal Hathi: 1 – “Seriously, we have to do formal headshots?”, 2 - Explaining, 3 – Explaining, and… 4 – Explaining. Today, I ask him to Explain some…
By Dany Hoter
(Download file here to follow along).
Time intelligence functions are some of the most important functions in DAX.
Being able to compare values between current period and the same period last year is a very common request and one that is a real challenge using native Excel and can easily achieved with DAX.
A typical calculated field might look like:
Sales Last Year:=CALCULATE(
In quite a few cases once you start using such expressions you will see this annoying error message:
The reason is that the time intelligence functions like the one used here SAMEPERIODLASTYEAR require 100% completely consecutive dates in the filter context, with no holes.
By Dany Hoter
Intro by Avi: I have often been asked, about ways to provide an “input” to the Power Pivot model from Excel. Disconnected Slicers are a popular way to do this. But with Excel and Power Pivot, there is always more than one way to accomplish a task. Dany shows us how, while making histograms easier to use. He uses a filter dropdown, which even works with Excel Online – inside a browser! Here is the end result, read on to learn how and download file.
Take it away Dany…
Creating a histogram in Excel based on Power Pivot is not as easy as it should be.
The method I use is no different from what others have already blogged and wrote about. There is even a solution that calculates the number of bins in a histogram with a formula that is based on the total number of cases.
My take on the problem was to let the user choose in run time what is the interval between each bin as a percentage and to show the number of bins accordingly.
The model contains data about a service that is in its infancy and so the users experience a relatively high number of errors.
The managers responsible for the service posed the following request:
“We want to see a histogram of the sessions showing how many users have experienced no errors in all the sessions they initiated, how many experienced errors in 10% of the sessions, 20% of the sessions … all the way to these poor users who saw nothing but errors in 100% of the sessions (Told you it is in early stage…)
Post By Dany Hoter Intro from Rob: In this post, Dany demonstrates how we can use Power Query to add a numerical index column to our table, and then use that to address the previous row in a calc column. …
As I have started working with clients around training/consulting, I am surprised by how often I find tables that have been flattened, i.e. the data tables have been combined (joined) with lookup tables, to produce a really wide table with everything and the kitchen sink in it. Maybe that’s to be expected; after all that is the modus operandi when working in Excel. That’s the first habit that I try to break. I would nominate this for the top 5 mistakes but that already has 6 on the list 🙂
Usually it is just a matter of changing how data is being pulled from the source to address this issue, and go from flat table to separate Data and Lookup tables (Star Schema). However at times, your data source itself may only have the flattened version of data. If you have little control/influence over the data source, you may be stuck with a flat table. Or not!
Flat to Star: Using DAX Query
The first thing that should come in your mind to deal with this should be Power Query, and we will cover that. But this time let’s try to use DAX as a query language for this purpose. Our sample data, is a flattened file which has sales data as well as product attributes. We would like to separate these into distinct power pivot tables.
The hardest part is getting started; for that watch the video. I will skip to the DAX query used, with a tip of the hat to Marco and Alberto from whom I have learned so much (and continue to do so).
Post by Rob Collie
If We Use Excel’s Built-In Top N Filter to See Our Top 1,000 Customers, It Hides the Other Customers Completely. But Using DAX, We Can Just “Split” the Audience into Two Groups.
This Came Up Recently…
Hey, I absolutely ADORE the TOP N filter capability offered by all Excel Pivots. It kicks major booty and we use it all the time:
The Top 10 / Top N Value Filter in Pivots: Get to Know It, It Does Amazing Things
But If we set that to show us the top 10 customers, it shows us JUST those 10 customers:
OK cool, we see those top ten customers, and that they collectively purchased $132,026 of stuff from us.
But we want to ALSO see how much the OTHER (non top 10) customers are worth too.
In my last post I talked about a mistake I made early on in my DAX learning journey. In that post I showed a formula that used CALCULATE ( ) to turn a row context into a filter context (AKA context transition). Here is a quick refresh of the relevant part of that post.
Since this post, I have been thinking about CALCULATE ( ) and wondering how to explain “WHY” CALCULATE ( ) creates context transition – this is the topic of today’s post.
I am sure there is a wide age profile of readers of this blog, and at least some of you would remember the Merrie Melodies cartoon “Cheese Chasers” where the dog (Marc) is sitting and using an ACME adding machine to make sense of what he is observing. [I know I said Looney Tunes in the title, but there is not a lot of difference and I thought more people would know what Loony Tunes was]. Before you watch this brief 40 second clip from the cartoon, let me first set the scene. Everything is backward; the mice don’t eat cheese, the cat WANTS to be chased by the dog – you get the idea. Spoiler alert – I will refer to the punch line below, so don’t read on until you watch the video if you want the full 1951 immersive experience.