Coffee Talk

Coffee Talk

Welcome to the latest P3 Adaptive 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 P3 Adaptive , 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 P3 Adaptive , 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 P3 Adaptive 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