Let’s start off today’s post with a pop quiz: Once you’ve answered, click here to see the poll results, and then scroll down to see the correct answer. … ... … The correct answer is C. DATEADD is a table…
Welcome to the latest PowerPivotPro Coffee Talk, where members of the community discuss various topics related to Power BI, Power Pivot, and Analytics/BI in general. These conversations take place during the week on a Slack channel, and are then lightly…
I remember feeling that way, Mr. Badger, but they’re simple… if you can SEE them! Have you ever needed to SUMX() over the values in more than one column? SUMMARIZE() can do it! I tried solving this problem and the…
Welcome back P3 nation, today I’d like to demonstrate some exciting ways to show Top Performing "things" using DAX. Most of what we’ll discuss has been covered before in previous posts written here at PowerPivotPro. Rob has extensively covered the…
A little automation goes a long ways! Hello P3 Nation, (can this be a thing?)…I’m excited to show you a piece of software that I’m confident near 100% of you will utilize! I’m here to talk to you about an…
I’ve been meaning to blog about this for a while - today's the day :-). Inside Power BI Desktop is a relatively new feature called "New Table". This is only available in Power BI Desktop and not in any of…
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…
by Matt Allington 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…
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).