PowerPivotPro

PowerPivotPro is Coming to Phoenix

February 20 - 22, 2018

Registration for 2018 Public Training is now open!

AVAILABLE CLASSES

**Use the discount code “3ORMORE” when signing up 3 or more people.

FEBRUARY 20 - 21

Foundations: Power Pivot & Power BI

Instructor: Kellan Danielson

Super charge your analytics and reporting skills with Microsoft’s dynamic duo. Designed to handle huge volumes of data, these tools will transform the way you work! Two Days in our class and you are EMPOWERED!

  • Learn Microsoft’s secret weapon behind Power Pivot & Power BI: DAX
  • Taught by Kellan Danielson – PowerPivotPro Partner and Vice President of Client Services
  • You don’t need to be an IT professional – most of our students come from an Excel background

FEBRUARY 20 - 21

Level Up Series: Advanced DAX

Instructor: Ryan Sullivan

The Advanced DAX Course was such a hit in the first half of 2017 that we’ve expanded the course to 2 days!

Overview

  • This advanced DAX training class is taught completely in Power BI Desktop.
  • Students are encouraged to take our Foundations course and have hands on experience with the DAX language.
  • Taught by Ryan Sullivan – Principal Consultant.
  • Class material drawn from usage of Advanced DAX applications while consulting with hundreds of international firms.

FEBRUARY 22

Level Up Series: Power Query for Excel & Power BI

Instructor: Krissy Dyess

The second class in the series is our Level Up Series is Power Query for Excel & Power BI.

  • Students are encouraged to take our Foundations course and have hands on experience with Power Query in Excel or Power BI Desktop.
  • Taught by Krissy Dyess – PowerPivotPro Principal Consultant and Phoenix native!
  • We will cover common to unique business challenges made easy with Power Query’s data wrangling capabilities.
  • Intermediate to Advanced Level Power Query best practices distilled into easy to understand patterns to apply to your most common business challenges.
PowerPivotPro Logo

Coffee Talk

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 edited for publication. In this installment of our Coffee Talk series, we’ll be chatting with DAX heavy hitters: Austin Senseman, Matt Allington, Kasper de Jonge, and Marco Russo. Let’s introduce everyone.

headshot - smaller

 

 

Kasper de Jonge, Program Manager at the Power BI team at Microsoft and passionate BI practitioner with deep knowledge of the Business intelligence market, specializing in Microsoft BI, Cortana Analytics and Azure data stack.  As an active member of the Microsoft BI community he shares his findings and knowledge on Power BI through his blog https://www.kasperonbi.com, several white papers and sessions at conferences like SQLPass, SQLBits, Ignite and many other events around the world.

Marco thumbnail

Marco Russo, Founder at SQLBI, Business Intelligence consultant and mentor.  His main activities are related to data warehouse relational and multidimensional design, but he is also involved in the complete development lifecycle of a BI solution.   Marco is also an author and, in addition to his many BI-related publications, has authored books about .NET programming. He is also a speaker at international conferences such as TechEd, PASS Summit, SQLRally, and SQLBits.

Matt Allington,  BI Professional with over 30 years’ experience in the Consumer Packaged Goods industry, Principal Consultant here at PowerPivotPro, owner of the company Excelerator BI and author of “Learn to Write DAX – A Practical Guide to Learning Power Pivot for Excel and Power BI” and “Supercharge Power BI – Power BI is Better When You Learn to Write DAX”.


Austin Senseman, VP Operations here at PowerPivotPro, trying to keep this company running like a well-oiled machine

Welcome and Introductions

austin
2017-09-06 12:00
has joined #coffeetalk_daxpros

 

matt
2017-09-06 12:19
has joined #coffeetalk_daxpros

 

Marco thumbnail

marco
2017-09-06 13:06
has joined #coffeetalk_daxpros

 

headshot - smaller

kasper
2017-09-06 13:12
has joined #coffeetalk_daxpros

 

austin
2017-09-07 10:01
Hello everybody!

 

matt
2017-09-07 10:01
Hi

 

matt
2017-09-07 10:02
how are you Austin?

 

austin
2017-09-07 10:03
Doing well – it’s the season of pumpkin spice latte and football here in the US. That’s all exciting.

 

austin
2017-09-07 10:03
How have you been?

 

Marco thumbnail

marco
2017-09-07 10:04
Oh this is the channel

 

Marco thumbnail

marco
2017-09-07 10:04
Never used Slack sorry

 

austin
2017-09-07 10:04
Hi Marco, welcome!

 

Marco thumbnail

marco
2017-09-07 10:04
Hello guys

 

austin
2017-09-07 10:05
We’ll give Kasper a few minutes to join us

 

Marco thumbnail

marco
2017-09-07 10:05
ok

 

Marco thumbnail

marco
2017-09-07 10:05
Are we going to use the chat or voice?

 

austin
2017-09-07 10:06
we’re just going to chat, so I don’t have to transcribe later.

 

headshot - smaller

kasper
2017-09-07 10:07
I’m here.

 

Marco thumbnail

marco
2017-09-07 10:07
ok

 

Marco thumbnail

marco
2017-09-07 10:08
Hi Kasper

 

headshot - smaller

kasper
2017-09-07 10:08
Hi guys

 

matt
2017-09-07 10:08
Hi Kasper, Marco

 

matt
2017-09-07 10:08
sorry, lots going on here in Oz

 

matt
2017-09-07 10:08
watching the Aussie Rules Football

 

matt
2017-09-07 10:08
my team is in the finals

 

matt
2017-09-07 10:09
support calls to my family for PCs

 

matt
2017-09-07 10:09
etc

 

 

matt
2017-09-07 10:09
How is Europe? Are you in Eurpoe?

 

headshot - smaller

kasper
2017-09-07 10:09
Rainy

 

Marco thumbnail

marco
2017-09-07 10:09
End of summer, start to cool down in Southern Europe and rain as always in the North.

 

headshot - smaller

kasper
2017-09-07 10:10
winter is coming ..

 

matt
2017-09-07 10:10
Next week I have 28, 29, 23 for Mon – Wed

 

matt
2017-09-07 10:10
come on summer!

 

Marco thumbnail

marco
2017-09-07 10:10
It’s supposed to be winter there..

 

matt
2017-09-07 10:10
just finished last week

 

Marco thumbnail

marco
2017-09-07 10:10
Good!

 

austin
2017-09-07 10:11
Alright first question, where are you located right now and what time is it?

 

matt
2017-09-07 10:12
Sydney, Aus 8:10pm

 

headshot - smaller

kasper
2017-09-07 10:12
Utrecht, Netherlands 12 AM

 

Marco thumbnail

marco
2017-09-07 10:12
Turin, Italy, 12:12pm

 

Marco thumbnail

marco
2017-09-07 10:12
(Kasper I think it’s 12pm also there :))

 

headshot - smaller

kasper
2017-09-07 10:12
haha yes

 

matt
2017-09-07 10:12
I think I have been there Kasper

 

austin
2017-09-07 10:12
Montgomery, AL, USA 5:10AM

 

austin
2017-09-07 10:12
haha

 

Marco thumbnail

marco
2017-09-07 10:13
Good morning!

 

matt
2017-09-07 10:13
Really Austin?

 

austin
2017-09-07 10:13
Need coffee.

 

matt
2017-09-07 10:13
You are up early.

 

matt
2017-09-07 10:13
That’s dedication.

 

austin
2017-09-07 10:13
Look it was hard enough to get all three of you together at one time and this is what worked.

 

Marco thumbnail

marco
2017-09-07 10:13
I know.

 

Marco thumbnail

marco
2017-09-07 10:14
Different time zones are challenging.

 

matt
2017-09-07 10:14
Marco, I have a question for you.

 

Marco thumbnail

marco
2017-09-07 10:14
Of course.

 

matt
2017-09-07 10:14
I notice on your email mail outs

 

matt
2017-09-07 10:14
you seem to have a lot of training courses

 

matt
2017-09-07 10:14
are you doing more

 

matt
2017-09-07 10:14
than in the past?

 

matt
2017-09-07 10:14
ie have things picked up?

 

austin
2017-09-07 10:15
Marco, how about you introduce yourself as well, and then we’ll each do an introduction

 

Marco thumbnail

marco
2017-09-07 10:17
Let’s start with the introduction. I’m Marco Russo, I have been working in BI since before Microsoft launched OLAP Services in SQL Server 7, and I always followed this market, regardless of the name (BI, BA, ML, DSS, whatever…). I have my roots as a developer (C++, Delphi, C#, Win32, .NET). I wrote several books about DAX and Tabular.

 

Marco thumbnail

marco
2017-09-07 10:18
@matt: we delivered more courses in 2017 because we started to cover US, but overall the total number of courses in 2017 will be slightly more than 2016

 

matt
2017-09-07 10:18
OK

 

matt
2017-09-07 10:18
I have noticed a pickup in interest in training in 2017

 

matt
2017-09-07 10:19
lots more people interested

 

austin
2017-09-07 10:19
Introductions!

 

headshot - smaller

kasper
2017-09-07 10:19
My name is Kasper de Jonge, I am a Principal Program Manager at the Power BI team working in the customer success team and have also worked on a lot of the different products like Power Pivot, PBI desktop and ofcourse the AS engine. Before that I was a BI consultant building MOLAP cubes and SSRS reports.

 

headshot - smaller

kasper
2017-09-07 10:19
Bam!

 

headshot - smaller

kasper
2017-09-07 10:19
owned DAX for a while as well

 

Marco thumbnail

marco
2017-09-07 10:20
@matt: An explanation is that Power BI is growing very quickly and this generates demand for training, too.

 

matt
2017-09-07 10:20
And I am Matt Allington, independent Power BI Consultant based in Sydney Australia. I do freelance consulting and training for anyone that wants to learn about Power BI.

 

matt
2017-09-07 10:21
@marco I agree. The interest in PowerBI as a product (as opposed to Power Pivot) is what is driving it

 

matt
2017-09-07 10:21
as shown on my Google trends blog here https://exceleratorbi.com.au/power-pivot-global-interest/

 

austin
2017-09-07 10:22
All of those introductions are so humble, which I appreciate, but I think everyone reading this will recognize that you guys are at the top of your field when it comes to this stuff.

 

matt
2017-09-07 10:23
You know one thing that I notice in this field is that the more you do, the better you get

 

matt
2017-09-07 10:23
not everyone gets the luxury to spend so much time with the tools

 

matt
2017-09-07 10:23
We do get that luxury

 

headshot - smaller

kasper
2017-09-07 10:23
yes that is why I keep up my blog

 

matt
2017-09-07 10:24
It is hard enough keeping up with the new features

New DAX features (as of Sep. 2017)

austin
2017-09-07 10:24
let’s talk about new features, since ppl will find that exciting – what new stuff this year has got you excited – specifically about DAX

 

headshot - smaller

kasper
2017-09-07 10:25
variables I think is the best for me

 

Marco thumbnail

marco
2017-09-07 10:25
Uhm, this year I didn’t see much news about DAX (variables are old – 2015 thing!). the IN operator in 2017 is the best new feature, probably

 

headshot - smaller

kasper
2017-09-07 10:25
I love the stuff you can do with them, although that is nothing you couldn’t really do before

 

matt
2017-09-07 10:26
Little things excite me

 

Marco thumbnail

marco
2017-09-07 10:26
Kasper, I disagree

 

matt
2017-09-07 10:26
like Measures on Rows

 

Marco thumbnail

marco
2017-09-07 10:26
Variables enable you constructs that are almost “impossible” without them

 

matt
2017-09-07 10:26
I guess measures on rows is not really DAX

 

Marco thumbnail

marco
2017-09-07 10:26
Power BI feature – and PivotTables had that for a while.

 

headshot - smaller

kasper
2017-09-07 10:26
yes there are some cool things you can do now, but interestingly enough I am not seeing that too often yet

 

matt
2017-09-07 10:27
Frankly most of the new DAX I don’t use. I find that it already does most of what I need

 

Marco thumbnail

marco
2017-09-07 10:27
Take a look at the calculated table here: https://github.com/marcosqlbi/DaxDateTemplate

 

Marco thumbnail

marco
2017-09-07 10:27
700 lines

 

Marco thumbnail

marco
2017-09-07 10:27
impossible to write it without variables

 

matt
2017-09-07 10:27
but sometimes new features like GENERATESERIES and SELECTEDVALUE make stuff easier

 

headshot - smaller

kasper
2017-09-07 10:28
yes that is why I like variables it makes it easier, much easier

 

Marco thumbnail

marco
2017-09-07 10:28
More important, variables are fundamental for optimization

 

headshot - smaller

kasper
2017-09-07 10:28
agreed there as well

 

Marco thumbnail

marco
2017-09-07 10:28
The engine too often recalculates measures that could reuse

 

austin
2017-09-07 10:28
Ok, so variables. This is one i really like – is it completely new? no. Completely awesome, yes. @marco How would you describe what a variable does to someone who’s not using them yet?

 

matt
2017-09-07 10:29
@marco

 

matt
2017-09-07 10:29
just saw your date table

 

matt
2017-09-07 10:29
wow!

 

Marco thumbnail

marco
2017-09-07 10:29
Variables are a way to split a calculation in smaller steps. Just as you would do with multiple calculated columns, but without having to store them and with a much more powerful expressivity.

 

matt
2017-09-07 10:29
Very nice! Yes I can’t see you doing that in a single line text editor

 

matt
2017-09-07 10:30
BTW, did you use SSMS or DAX STudio?

 

Marco thumbnail

marco
2017-09-07 10:30
Thanks – feedback for date table is very welcome, in particular if you can provide me the settings for the weekly fiscal calendar commonly used in Australia it would be very useful

 

Marco thumbnail

marco
2017-09-07 10:30
DAX Studio

 

Marco thumbnail

marco
2017-09-07 10:31
SSMS is not ready (my point of view)

 

matt
2017-09-07 10:31
Aussies, typically have 30 June as last day of year

Old vs New DAX

austin
2017-09-07 10:31
I want to pick up on a comment Matt made “Frankly most of the new DAX I don’t use. I find that it already does most of what I need” – I find that to be true as well.

 

austin
2017-09-07 10:31
This is a good thing for us teachers right?

 

austin
2017-09-07 10:32
The DAX of 2010 isn’t all that different from 2017

 

matt
2017-09-07 10:32
Maybe some of the harder stuff is just not something my customers need

 

Marco thumbnail

marco
2017-09-07 10:32
Well, first of all the variables are not used because there are not many examples around

 

matt
2017-09-07 10:33
The thing I would like the most in Excel is actually New Table

 

matt
2017-09-07 10:33
I love that thing

 

matt
2017-09-07 10:33
I love materializing tables so I can “see” what is happening

 

austin
2017-09-07 10:33
ha, @matt yes that’s a nice feature

 

headshot - smaller

kasper
2017-09-07 10:33
Most of the new features of DAX come to life due to UI features getting added to Power BI or variables for performance reasons

 

Marco thumbnail

marco
2017-09-07 10:33
I and Alberto Ferrari are committed to use them heavily in all the future books and articles – we actually started this a few months ago

 

matt
2017-09-07 10:33
Take SelectedColumns

 

matt
2017-09-07 10:33
or what ever it is called

 

matt
2017-09-07 10:33
I see Marco’s posts about it

 

matt
2017-09-07 10:34
and I have started to read it a few times

 

matt
2017-09-07 10:34
but I keep going back to SUMMARIZE and ADDCOLUMNS

 

matt
2017-09-07 10:34
maybe I am confusing the new DAX formula, but that highlights the point

 

matt
2017-09-07 10:34
I have something that works

 

matt
2017-09-07 10:34
and I like it

 

matt
2017-09-07 10:34
it takes a bit to change what I know works

 

Marco thumbnail

marco
2017-09-07 10:35
I think we have two generations of DAX

 

austin
2017-09-07 10:35
oh, go on …

 

Marco thumbnail

marco
2017-09-07 10:35
Before SuperDAX and After SuperDAX

 

austin
2017-09-07 10:35
haha i like it

 

Marco thumbnail

marco
2017-09-07 10:35
We worked for years without SuperDAX and most of the examples around are using it

 

matt
2017-09-07 10:36
SUMMARIZECOLUMNS

 

Marco thumbnail

marco
2017-09-07 10:36
When SuperDAX arrived, it wasn’t supported widely, so we had to wait for Excel 2016 + SSAS 2016 + Power BI to be adopted enough before starting to use them

 

matt
2017-09-07 10:36
is what I was thinking of, not selected columns

 

Marco thumbnail

marco
2017-09-07 10:36
So now it’s that time, and we are starting using it. But as humans, it’s normal to be stuck to old habits.

 

matt
2017-09-07 10:36
So what was the defining moment for SuperDAX?

 

Marco thumbnail

marco
2017-09-07 10:37
Not a precise moment, but I would say after SP1 for SSAS 2016 and after 12/18 months after release of Excel 2016

 

matt
2017-09-07 10:37
I think new functions like SELECTEDVALUE can add a lot of value

 

Marco thumbnail

marco
2017-09-07 10:37
SuperDAX actually was here since first versions of Power BI

 

headshot - smaller

kasper
2017-09-07 10:38
Superdax introduces a slew of new DAX functions to improve performance between Power BI and the AS engine inlcuding things like variables

 

matt
2017-09-07 10:38
It is pretty hard to explain to someone why they need IF(HASONEVALUE(table[Column]),VALUES(Table[Column])).

 

Marco thumbnail

marco
2017-09-07 10:38
We included it in The Definitive Guide to DAX book – but we didn’t use it so much, otherwise the book would have been useless to all the readers not using older versions.

 

matt
2017-09-07 10:38
I taught a class today using SELECTEDVALUES and GENERATESERIES for the first time.

 

matt
2017-09-07 10:38
So much easier.

 

matt
2017-09-07 10:38
They all got it immediately.

 

headshot - smaller

kasper
2017-09-07 10:39
I think the main problem today is still the core concepts, I had to point to a post from 2010 today. It is not so much around the functions themselves.

 

matt
2017-09-07 10:39
@kasper, so var makes things more efficient?

 

matt
2017-09-07 10:39
I realise it is easier to read

 

matt
2017-09-07 10:39
and write.

 

headshot - smaller

kasper
2017-09-07 10:39
@matt yes instead of executing it multiple times you can store the result in a variable.

 

matt
2017-09-07 10:40
but for example

 

matt
2017-09-07 10:40
filter(table, yada)

 

matt
2017-09-07 10:40
this is storage engine

 

matt
2017-09-07 10:40
and if repeated, it should be cached, right/

 

matt
2017-09-07 10:40
?

 

headshot - smaller

kasper
2017-09-07 10:41
this is a good example
Measurename = VAR var1 = EXPRESSION
VAR var2 = EXPRESSION
VAR var3 = IF(var1 > 10, var1 / var2, var2)RETURN var3

 

Marco thumbnail

marco
2017-09-07 10:41
The entire thing of DAX is the following: it has concepts that don’t exists in *any* other language. So developers and business users who already learnt something (VBA, R, C, SQL, …) try to apply their existing knowledge to something that works in a different way. And they don’t understand why a function doesn’t work as they expect. They assume they know how the DAX language should work, when they never worried about checking whether it’s different or not.

 

matt
2017-09-07 10:41
I agree

 

matt
2017-09-07 10:41
!!

 

headshot - smaller

kasper
2017-09-07 10:41
if you would have written everything out fully Var1 would have been executed twice

 

matt
2017-09-07 10:41
This is what keeps me in a job!

Common Student Struggles

austin
2017-09-07 10:42
What are the handful of concepts that students struggle with the most, let’s just list some

 

matt
2017-09-07 10:42
@kasper

 

Marco thumbnail

marco
2017-09-07 10:42
The funny thing is that also MDX was different. But because it has a completely different syntax (thinking to SCOPE) people realized there was something to study. DAX tried to resemble Excel formulas, and here is where the issue started.

 

matt
2017-09-07 10:42
interesting

 

matt
2017-09-07 10:43
what about the multiple IF thing

 

matt
2017-09-07 10:43
or switch

 

matt
2017-09-07 10:43
I seem to remember Marco teaching me that every leg of a switch gets evaluated

 

Marco thumbnail

marco
2017-09-07 10:43
filter context and context transition.

 

matt
2017-09-07 10:43
even if only 1 leg is returned

 

headshot - smaller

kasper
2017-09-07 10:43
@matt that has been fixed

 

matt
2017-09-07 10:43
ohhh

 

matt
2017-09-07 10:43
really

 

matt
2017-09-07 10:43
great

 

matt
2017-09-07 10:43
just in SuperDAX?

 

matt
2017-09-07 10:43
Power BI

 

Marco thumbnail

marco
2017-09-07 10:44
@kasper I would not swear that it always works by the way!

Marco thumbnail

marco
2017-09-07 10:44
Most of the time, yes

 

headshot - smaller

kasper
2017-09-07 10:44
@matt SSAS 2016 and Power BI and I also believe Excel 2016

 

headshot - smaller

kasper
2017-09-07 10:45
I think Marco covered the list.. it seems like a small list but it is hard to grasp

 

austin
2017-09-07 10:45
you mean re: hard topics for students?

 

headshot - smaller

kasper
2017-09-07 10:46
for everyone

 

headshot - smaller

kasper
2017-09-07 10:46
yes

 

matt
2017-09-07 10:46
I actually do like var, and I don’t use it enough

 

matt
2017-09-07 10:46
to me I get excited with VAR when there are nested Row Contexts

 

matt
2017-09-07 10:46
to eliminate EARLIER

 

austin
2017-09-07 10:46
death to EARLIER!

 

Marco thumbnail

marco
2017-09-07 10:46
matt: every time you see a function calling another function, you might consider splitting the calculation using variables and documenting in this way what you are doing

 

matt
2017-09-07 10:46
I think many complain about the lack of a decent editor window

 

headshot - smaller

kasper
2017-09-07 10:47
but honestly the goal of the Power BI team is for folks to have to write less DAX for the cookie cutter cases

 

matt
2017-09-07 10:47
@marco I agree I should do this

 

matt
2017-09-07 10:47
like you said, bad habits

 

Marco thumbnail

marco
2017-09-07 10:47

 

austin
2017-09-07 10:48
I’m really trying to get students to consider that they are now programmers and they need to develop some good habits, ppl coming from the Excel world aren’t thinking this way

 

Marco thumbnail

marco
2017-09-07 10:48
Kasper, as a consultant I’m happy about what MS is doing, but I think that strategically that is not the right thing to do.

 

matt
2017-09-07 10:48
@marco why do you say that?

 

austin
2017-09-07 10:48
yes, go on

 

matt
2017-09-07 10:49
I don’t like it either

 

matt
2017-09-07 10:49
but couldn’t claim to have a strategic reason why it shouldn’t do so

 

matt
2017-09-07 10:50
Personally I prefer a UI driven supportive framework

 

matt
2017-09-07 10:50
like with M

 

Marco thumbnail

marco
2017-09-07 10:50
Well, you can try to create some candy functions that solve specific problems, but usually there functions hide some complexity and might introduce some unexpected behavior that requires more work to be solved for an unexperienced user. Providing more examples to users would work better, and MS would save money by investing there rather than in new functions that have to be supported later.

 

matt
2017-09-07 10:50
Let the UI do the work, but don’t preclude those that want to dig deeper

 

matt
2017-09-07 10:50
I think we are saying similar things Marco

 

matt
2017-09-07 10:50
I think help is good, but I like to see what is happening

 

Marco thumbnail

marco
2017-09-07 10:51
I agree with you, Matt, but the key to leverage the community is to enable smart templates to be included in the product. The Quick Gallery doesn’t work well for this.

 

matt
2017-09-07 10:51
this is why I don’t like drag and aggregate columns

 

austin
2017-09-07 10:51
Nothing about the M _language_ excites me, but that’s another coffee talk …

 

matt
2017-09-07 10:51
ohhh, nooo

 

matt
2017-09-07 10:51
Austin

 

matt
2017-09-07 10:51
I have learn’t to love it

 

matt
2017-09-07 10:51
the only thing wrong with M is the lack of intellisense

 

matt
2017-09-07 10:52
If Miguel ever fixes that puppy

 

matt
2017-09-07 10:52
M will be off and running

 

headshot - smaller

kasper
2017-09-07 10:52
A big reason for some customers to not even look at PBI is that they HAVE to write DAX for everything. Now they don’t have to anymore.

 

matt
2017-09-07 10:52
It is just not practical for the average Excel person to learn this language without intellisense

 

headshot - smaller

kasper
2017-09-07 10:52
I agree I would like more debugging capabilities as well but that will come in time

 

matt
2017-09-07 10:52
I have seen the intellisense

 

matt
2017-09-07 10:53
can you ask Miguel where it is?

 

matt
2017-09-07 10:53
he said it was “coming soon”

 

matt
2017-09-07 10:53
in May 2016

 

 

austin
2017-09-07 10:53
No more M talk, haha

 

Marco thumbnail

marco
2017-09-07 10:53
Quick Gallery could work much better – this is what I meant. Today it’s a small toy.

 

headshot - smaller

kasper
2017-09-07 10:53
Marco one step at the time.

 

matt
2017-09-07 10:53
I don’t like the inbuilt date tables

 

matt
2017-09-07 10:53
I hate them

 

matt
2017-09-07 10:53
with a passion

 

matt
2017-09-07 10:53
sorry!

 

matt
2017-09-07 10:54
I just hate them

 

Marco thumbnail

marco
2017-09-07 10:54
Matt: this is why I started the DaxTemplate project. I want to start any new project with that template. The auto date/time is disabled, I have a single Date and I can use it

 

matt
2017-09-07 10:54
and that [Date].[Date] thingy too

 

matt
2017-09-07 10:54
yes, agree @marco

 

Marco thumbnail

marco
2017-09-07 10:55
I measured the time lost to fix the date table in any new project

 

Marco thumbnail

marco
2017-09-07 10:55
15-20 minutes

 

Marco thumbnail

marco
2017-09-07 10:55
Crazy

 

Marco thumbnail

marco
2017-09-07 10:55
So I made the decision

 

matt
2017-09-07 10:55
Kasper, why?

 

matt
2017-09-07 10:55
What is the thinking?

 

matt
2017-09-07 10:55
just to try to make it seamless?

 

matt
2017-09-07 10:55
for all users

 

matt
2017-09-07 10:55
who don’t know how to use a date table?

 

headshot - smaller

kasper
2017-09-07 10:56
yes or even create a date table

 

Marco thumbnail

marco
2017-09-07 10:56
But why creating one table for every date?

 

matt
2017-09-07 10:56
I kind of get it, but I don’t think creating a calendar table is any harder than any other table of data

 

Marco thumbnail

marco
2017-09-07 10:56
It works only if you have one table

 

Marco thumbnail

marco
2017-09-07 10:56
WIth multiple tables, it creates more issues

 

headshot - smaller

kasper
2017-09-07 10:58
it is designed for the simple scenario where a user is forced to create a date table to use a year or month

 

Marco thumbnail

marco
2017-09-07 10:58
Yes, but polluting the model with tens of date table is not a good idea.

 

matt
2017-09-07 11:00
Don’t start me about defaults!

 

Marco thumbnail

marco
2017-09-07 11:00
e.g. create a single global one instead of one for each date

 

Marco thumbnail

marco
2017-09-07 11:00
give me a setting

 

matt
2017-09-07 11:00
I also don’t like default auto detect relationships

 

austin
2017-09-07 11:00
Can we pause for a second and agree that DAX may be one of the best things that Microsoft has ever produced

 

matt
2017-09-07 11:00
I don’t want any software developer interfering with my relationships!!

 

matt
2017-09-07 11:01
Oh, yeah – there is that @austin

 

Marco thumbnail

marco
2017-09-07 11:01
Well MS produced many nice things

 

DAX Love

Marco thumbnail

marco
2017-09-07 11:01
I love DAX

 

matt
2017-09-07 11:01
I tell you why I love DAX

 

Marco thumbnail

marco
2017-09-07 11:01
but I also love MDX and I had a long story with .NET

 

matt
2017-09-07 11:01
and also why so may people I meet love DAX

 

matt
2017-09-07 11:02
is because it was written for people like me

 

matt
2017-09-07 11:02
(sorry, you too Marco)

 

matt
2017-09-07 11:02
but for me

 

matt
2017-09-07 11:02
I have always been a business data guy

 

matt
2017-09-07 11:02
but the really powerful tools were very complex

 

matt
2017-09-07 11:02
like MDX

 

matt
2017-09-07 11:02
fine if your name is Marco or Kasper

 

matt
2017-09-07 11:02
but not if your a business guy that loves data

 

matt
2017-09-07 11:02
DAX is not like that

 

matt
2017-09-07 11:03
I can learn it

 

matt
2017-09-07 11:03
I can do it

 

matt
2017-09-07 11:03
myself without the need for IT help

 

Marco thumbnail

marco
2017-09-07 11:03
Got it

 

austin
2017-09-07 11:03
There is a learning curve though with DAX, i don’t think it’s fair to call it EASY but it is ACCESSIBLE

 

matt
2017-09-07 11:03
Definitely

 

matt
2017-09-07 11:03
that is why I have a job teaching people

 

matt
2017-09-07 11:04
because of the learning curve

 

matt
2017-09-07 11:04
Most people I teach don’t come from an IT background

 

headshot - smaller

kasper
2017-09-07 11:04
Excel formula’s are not easy either

 

matt
2017-09-07 11:04
and they don’t understand tables of data

 

matt
2017-09-07 11:04
they only understand formulas and cells

 

matt
2017-09-07 11:05
Excel people live in a 2 dimensional world

 

matt
2017-09-07 11:05
rows and columns

 

matt
2017-09-07 11:05
cell by cell

 

matt
2017-09-07 11:05
as soon as you introduce a virtual table (FILTER)

 

headshot - smaller

kasper
2017-09-07 11:05
I love it when Excel folks start to get there are relationships and you see the light go on!

 

matt
2017-09-07 11:05
a lot of people get lost

 

austin
2017-09-07 11:05
I find myself saying this A LOT to Excel ppl, “This DAX stuff is long-term EASIER than what you’re doing now (7 level nested if statement all crammed on one line in Excel), but it’s going to feel harder because it’s NEW.”

 

matt
2017-09-07 11:05
@kasper – agree

 

matt
2017-09-07 11:06
I say “these 4 relationships is the same as writing a VLOOKUP on every single column in every table

 

matt
2017-09-07 11:06
they are impressed

 

matt
2017-09-07 11:07
regarding things I like about MS at the moment

 

headshot - smaller

kasper
2017-09-07 11:07
but I think the harder part is getting them to even start looking at new ways of doing things

 

matt
2017-09-07 11:07
specifically

 

austin
2017-09-07 11:07
@kasper who are the personas in the MS world RE: DAX?

 

matt
2017-09-07 11:07
is the user voice

 

matt
2017-09-07 11:07
the fact that MS is taking feedback onboard

 

matt
2017-09-07 11:07
I think it is great

 

matt
2017-09-07 11:08
I remember Marco drove the feedback about bidrectional relationships – should not be default

 

matt
2017-09-07 11:08
and now it is changed

 

matt
2017-09-07 11:08
this type of thing makes a big difference

 

headshot - smaller

kasper
2017-09-07 11:08
Probably the person doing the most with DAX these days is Jeffrey Wang: https://pbidax.wordpress.com/author/jwang8888/

 

headshot - smaller

kasper
2017-09-07 11:08
this blog will really blow you mind

 

austin
2017-09-07 11:09
… everyone is scrolling through the blog right now …

 

headshot - smaller

kasper
2017-09-07 11:10
Marco knows about it

 

Marco thumbnail

marco
2017-09-07 11:10
Yes

 

matt
2017-09-07 11:11
I have previously subscribed

 

matt
2017-09-07 11:11
but I don’t get any notification of new articles

 

matt
2017-09-07 11:11
and hence I have missed all this.

 

matt
2017-09-07 11:11
I will have to try another way

 

matt
2017-09-07 11:11
life is so much easier when I can get an email telling me about a new article

 

headshot - smaller

kasper
2017-09-07 11:11
as I mentioned earlier there is not much active development in DAX as a language at the moment, It is more adding new functions to support new UI features, like IN. Some exceptions are around things like selectedvalues that a dev just decided to do.

 

headshot - smaller

kasper
2017-09-07 11:12
In the past with SuperDAX we had many PM’s and devs developing big parts of the language now a little less

 

headshot - smaller

kasper
2017-09-07 11:13
for example in the query generation part of Power BI to help build report level measures

 

Marco thumbnail

marco
2017-09-07 11:13
Matt: use feedburner for that

 

austin
2017-09-07 11:13
So let’s move toward wrapping up.

Wrapping Up

matt
2017-09-07 11:14
Yes, but where does feedburner go?

 

matt
2017-09-07 11:14
I never got the RSS thing

 

austin
2017-09-07 11:14
One at a time, starting with Marco, if you had 10,000 people in a room that were POTENTIAL DAX users what would you tell them in a few sentences to get them interested in DAX?

 

Marco thumbnail

marco
2017-09-07 11:17
I’m not a marketing guy so I would say them that DAX is powerful, you can express your business calculation in a few lines, but you need to spend some time to understand concepts that are unique to this language. Otherwise, you will use it as you use Excel, but you will not be able to get the most out of it, because you don’t have the same flexibility of writing numbers around as you have in Excel…

 

austin
2017-09-07 11:18
fair, that was sort of a marketing question. @marco what would you say to the people who’ve been doing DAX for a while, what should they focus on?

 

 

matt
2017-09-07 11:21
@marco, this is jeff?

 

Marco thumbnail

marco
2017-09-07 11:21
If you use DAX but sometime you cannot write what you want, or the result is not what you expect, review the basics: row context, filter context, and context transition. If you know them, simplify your code using variables

 

Marco thumbnail

marco
2017-09-07 11:21
@matt Yes it’s a mail subscription of the feed

 

matt
2017-09-07 11:21
thanks

 

austin
2017-09-07 11:21
@matt what would you say to the room of 10k potential DAX users in a few sentences?

 

matt
2017-09-07 11:21
I don’t think you can say anything to convince people. I don’t think it is “sayable”. You need to show them. Often people contact me asking for help to get Power BI going in their business. I just say, don’t waste your time trying to convince other people. Just start using the tools in your job and share what you learn. Sooner or later someone will look over your shoulder and say “How did you do that?”

 

austin
2017-09-07 11:22
i love that

 

matt
2017-09-07 11:22
people know what the limitations are

 

matt
2017-09-07 11:22
real people that use Excel

 

matt
2017-09-07 11:22
they know

 

matt
2017-09-07 11:22
the limits

 

headshot - smaller

kasper
2017-09-07 11:22
yeah seeing is believing

 

matt
2017-09-07 11:22
when they see you doing it easy, they will say “wow”

 

matt
2017-09-07 11:22
I am still amazed

 

matt
2017-09-07 11:22
in my training

 

matt
2017-09-07 11:23
I train 1.5 days on DAX

 

matt
2017-09-07 11:23
and then I train Power Query and show UNPIVOT

 

matt
2017-09-07 11:23
and people always go WOW

 

matt
2017-09-07 11:23
because they know how hard it is

 

austin
2017-09-07 11:24
@kasper how would you describe DAX to someone that wasn’t all that technical, for example what if you had to describe to my mom what Austin did for a living?

 

matt
2017-09-07 11:25
I would say “Not a lot”

 

 

austin
2017-09-07 11:25
ha!

 

headshot - smaller

kasper
2017-09-07 11:25
that is the hardest question :slightly_smiling_face: but something like DAX is a language that will allow you to analyse data and express business logic in a simple expression, it is based on Excel functions and uses some similar concepts so it is in reach for every excel user.

 

headshot - smaller

kasper
2017-09-07 11:26
but often examples are the best like it can solve issues like comparing sales of red bikes against all other bikes or year over year sales easily

 

austin
2017-09-07 11:27
yep, showing is really powerful, for the record, my mom tells people that i do “computers” for a living

 

austin
2017-09-07 11:28
This has been a great hour of my life, even if it is SUPER early. Thanks to each of you for jumping on and sharing.

 

austin
2017-09-07 11:28
DAX has been around for 7 years, but we’re just getting started.

 

austin
2017-09-07 11:29
Closing comments?

 

headshot - smaller

kasper
2017-09-07 11:29
For someone who has been using DAX for a while I would recommend two things: 1 start using variables 2 take a look at the queries that are generated by Power BI and learn from it.

 

matt
2017-09-07 11:31
@kasper, how to look at the queries? Using DAX STudio?

 

Marco thumbnail

marco
2017-09-07 11:31
I would add: follow coding rules, keep your code readable. A column reference should always have the table name, a measure reference shouldn’t. Queries generated by Power BI will be visible in an easy way with the next version of DAX Studio (eheheh) but they use a table name before measure reference, which is not something that I would suggest to users to do.

 

matt
2017-09-07 11:31
like I learnt from Marco?

 

Marco thumbnail

marco
2017-09-07 11:31
@matt: DAX Studio 2.7 will make it *much* easier

 

matt
2017-09-07 11:31
Do you mean the SQL queries?

 

matt
2017-09-07 11:31
@marco, oh, good

 

headshot - smaller

kasper
2017-09-07 11:31
@matt turn on tracing in diagnostic under the options menu

 

matt
2017-09-07 11:32
ohhhhh

 

matt
2017-09-07 11:32
I didn’t know about that

 

headshot - smaller

kasper
2017-09-07 11:32
now that I see marco’s tip I would like to add start using comments as well

 

matt
2017-09-07 11:33
@kasper, are the traces readable?

 

headshot - smaller

kasper
2017-09-07 11:33
it generates a profile trace that can be read with SQL Server profiler

 

austin
2017-09-07 11:33
Comments is the #1 thing I’m pushing on our clients that are already using DAX – “Here’s a great way to spend less money with consultants, document your DAX”

 

Marco thumbnail

marco
2017-09-07 11:33
@matt I can provide you a beta release of DAX Studio 2.7 so you avoid this pain

 

matt
2017-09-07 11:33
So I can’t read it myself?

 

matt
2017-09-07 11:34
I don’t know how to use profiler

 

matt
2017-09-07 11:34
I’m just an excel guy

 

matt
2017-09-07 11:34
That would be great Marco

 

matt
2017-09-07 11:34
thanks

 

headshot - smaller

kasper
2017-09-07 11:34
just double click and it opens it.

 

matt
2017-09-07 11:34
OK, I will take a look

 

austin
2017-09-07 11:35
Alright, good afternoon, good evening. I’ve got to go make some coffee. Everyone have a wonderful day.

 

headshot - smaller

kasper
2017-09-07 11:35
same to you! cya all

 

Marco thumbnail

marco
2017-09-07 11:35
@matt mail sent. For SQL Profiler you need to install SSMS 17.2 that includes SQL Profiler

 

Marco thumbnail

marco
2017-09-07 11:35
(it’s free)

 

Marco thumbnail

marco
2017-09-07 11:35
See you guys

 

Marco thumbnail

marco
2017-09-07 11:35
Bye Austin

 

Marco thumbnail

marco
2017-09-07 11:36
Bye Kasper and bye Matt

 

Marco thumbnail

marco
2017-09-07 11:36
I have to go

 

matt
2017-09-07 11:36
Bye all, nice chatting

 

matt
2017-09-07 11:36
My team is 50 points in front

 

matt
2017-09-07 11:37
3rd quarter

 

matt
2017-09-07 11:37
Looking good

 

Forget bending spoons with your mind – there’s no money in it.

It takes a special kind of mindset to “bend” data (and software!) to the human will.  As this article demonstrates, we at PowerPivotPro can twist Power BI into a pretzel if that’s what an organization needs. (A robust, trustworthy, industrial-strength pretzel of course).

The data-oriented challenges facing your business require BOTH a nimble toolset like Power BI AND a nimble mindset to go with it. And as Val Kilmer / Doc Holladay once said, we’re your huckleberry.

Connect with the Experts

 

  Subscribe to PowerPivotPro!
X

Subscribe

Austin Senseman

Austin spent the last five years using Modern Excel in the field to solve analytical challenges for the financial services industry, first at BBVA and later at Harbert Management Corporation. As a former analyst, Austin is focused on enabling other data professionals to experience the joys of Power Pivot & Power BI. Austin is a CFA Charterholder, former CPA, Excel Power User, and SQL Server Professional, whose involvement in data/analytics projects lowers the communication costs between business users and IT professionals and helps organizations get to solutions faster. 

This Post Has 2 Comments

Leave a Comment or Question