This Email and the Highlighted Text were Automatically Generated in Response to DAX “Detectors” Scanning Our Results During Scheduled Refresh I’ve Wanted this Feature Forever. We Now Have It. Classes Announced for Houston, Los Angeles, Indianapolis, and London First, we…
I was helping a friend out recently with an interesting problem. It all started with a SUM( ) that wasn’t behaving. It quickly became a SUMX( ) problem but evolved into a DAX Studio/Query problem. Let me explain.
The Root Problem – Same Store Sales
Many retail businesses open and close stores throughout any given year. This creates a problem because it is difficult to determine if business growth is vertical (increased sales within existing stores) or horizontal (expansion of the store base). It is common to do analysis on “same store sales” where you only include stores that had sales for the entire period this year and also last year. There are quite a few posts already on this topic on PowerPivotPro.com, but this is a good opportunity for me to talk about SUMX and also DAX as a query language. I have reconstructed the scenario with some test data. You can download the workbook here if you want to take a look.
I started off with some base measures as follows:
A. Total Sales:=SUM(Sales[Extended Amount]) B. Total Sales LY:=CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Calendar[Date]))
There are a few different ways to work out sales last year, but I have used SAMEPERIODLASTYEAR above and this works just fine in this instance.
Post by Rob Collie and Chris Finlan
Datazen (The Latest Addition to Microsoft’s Suite of BI Tools) is a Mobile Monster
(Monster in a GOOD Way. Yes, PowerPivotPro has its own DZ Custom Theme – You Can Too)
Datazen Q&A With Chris Finlan
***Intro from Rob: Today I’m interviewing Chris Finlan of Microsoft about MS’s recent acquisition of Datazen, and what this means to us in the Power Pivot and Power BI community.
ROB: Last month, Microsoft purchased a company named Datazen. Most people had never heard of Datazen before, but you had pointed them out to me last summer I think. You were already a big believer in them at that point, as were your customers.
CHRIS: Yeah, I love Datazen. I’m as passionate about it as you are about Power Pivot. I think it’s an extraordinary product, and have felt this way for quite some time Don’t believe me? Check out the date of my review in the Windows Store. (Spoiler alert: it was April of 2013 – that’s before I even applied for a job at Microsoft).
ROB: You’re truly a trendsetter in tech and clothing. I think one of the natural first reactions/questions from the community is, “wait, did MS just buy one of Power BI’s competitors, and if so, when do I use it versus, say, Power Pivot?”
CHRIS: No, DZ was designed from the beginning to “only” be a visualization layer on top of the Microsoft Data Platform. In your post on Visualizations Layers in Perspective: The Last Mile, you pointed out three key reasons at the end on why you’d buy a visualization tool. Datazen checks all three boxes (and oh by the way, there’s no longer anything to buy – it’s simply a benefit you receive when you license SQL Server Enterprise with Software Assurance).
ROB: Which means it’s free for many existing MS customers. More on that later. But I also want to talk about how DZ can be used to “light up” the great work being done by Power Pivot practitioners, because hey, that’s what we do around here.
Any Flat Table in Excel Can “Power” a Datazen Dashboard
EX: Power Pivot Produces a Flat Pivot (or DAX Query Table),
and DZ Can Use That Excel File as a First-Class Data Source.
(The ONLY Server Required Here is a DZ Server – No SharePoint, No SQL, No SSAS)
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).
Guest post by Scott Senkeresty After completing this post on table queries, I celebrated by curling up with Microsoft Excel 2013: Building Data Models with PowerPivot. It’s a great book by Marco Russo and Alberto Ferrari. (I’ve read Rob’s book…
Guest post by Scott Senkeresty
Get it? “Median?” SO Funny!
Rob is on-site with a client this week, so the reins, mic, baton or other appropriate metaphor gets handed to me today. We get to today’s topic by means of a discussion on calculating a median in DAX:
Scott: That sounds easy. Just use TopN to grab half the numbers in ascending order, then another TopN against descending values to grab the final value(s) (Glossing over odd vs even number of data rows)
Rob: What are you going to do about ties?
Scott: <Blank Stare>
It turns out that calculating a median in DAX is pretty tricky. Rob contends that sneaky street fighting tactics are required to deal with ties…where in my heart of hearts, I believe an elegant solution exists. Hopefully we can get to the bottom of that in a future blog post.
Guest post by Ken Puls
I was recently working on an interesting thing in PowerPivot, and thought I’d share it in case it may help someone… A quick summary of the issue is that I needed to examine multiple records in a child table, and return a single result to the parent table if a condition was present; something that goes against the normal relationship flow.
I have a file that tracks the purchase and sale of land, as well as the respective property taxes and assessments that we get on an annual basis. It is set up like this:
At first glance this might look complicated… Each Parcel is assigned a unique Parcel Identifier, known as a PID, a key identifier that will never change for a piece of property. This info is then used to identify that parcel by the tax authority, the assessment authority, and our appraisers and accounting systems. In the case above, we can see that the PID from the Parcel table links to the Transaction, Taxes and Assessments tables. From the other side we’ve got the Key_Date that links back to the three tables as well, although it links to TranDate, TaxYear and AssessYear respectively.
The Second Time I’ve Used This “Egg on My Face” Picture
OK, on Tuesday I published a technique that I’ve been using for years now – a way to get a list of all measure formulas as text. And it took about five minutes for people to respond and tell me about all the better ways to do it.
I’d like to say that none of those ways were better than mine. But ALL of them were better