Category Archives: Self-Service BI

Coffee Talk: DAX Pros

coffee talk 1 Coffee Talk: DAX Pros

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 thumb Coffee Talk: DAX Pros

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 thumb Coffee Talk: DAX Pros

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.

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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”.

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros
Austin Senseman, VP Operations here at PowerPivotPro, trying to keep this company running like a well-oiled machine

Welcome and Introductions

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

2017-09-07 10:01
Hello everybody!

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:01

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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.

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

2017-09-07 10:04
Hello guys

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

2017-09-07 10:05

Marco thumbnail thumb Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

2017-09-07 10:07

Marco thumbnail thumb Coffee Talk: DAX Pros

2017-09-07 10:08

headshot smaller thumb Coffee Talk: DAX Pros

2017-09-07 10:08

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:09

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

2017-09-07 10:09

Marco thumbnail thumb Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

2017-09-07 10:10

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

2017-09-07 10:12

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

2017-09-07 10:12

Marco thumbnail thumb Coffee Talk: DAX Pros

2017-09-07 10:13
Good morning!

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:13
Really Austin?

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

2017-09-07 10:13
Need coffee.

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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 thumb Coffee Talk: DAX Pros

2017-09-07 10:13

Marco thumbnail thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

2017-09-07 10:14
Of course.

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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 thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:18

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

2017-09-07 10:19
Introductions!

headshot smaller thumb Coffee Talk: DAX Pros

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 thumb Coffee Talk: DAX Pros

2017-09-07 10:19

headshot smaller thumb Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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.

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:21

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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.

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

New DAX features (as of Sep. 2017)

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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 thumb Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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 thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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 thumb Coffee Talk: DAX Pros

2017-09-07 10:27

Marco thumbnail thumb Coffee Talk: DAX Pros

2017-09-07 10:27

Marco thumbnail thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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?

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:29

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:29

Marco thumbnail thumb Coffee Talk: DAX Pros

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.

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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 thumb Coffee Talk: DAX Pros

2017-09-07 10:30
DAX Studio

Marco thumbnail thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Old vs New DAX

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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.

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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 thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:33
Take SelectedColumns

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:36
SUMMARIZECOLUMNS

Marco thumbnail thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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.

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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 thumb Coffee Talk: DAX Pros

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.

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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.

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:39
and write.

headshot smaller thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:40
but for example

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:40

headshot smaller thumb Coffee Talk: DAX Pros

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 thumb Coffee Talk: DAX Pros

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.

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:41

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:41

headshot smaller thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Common Student Struggles

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:42

Marco thumbnail thumb Coffee Talk: DAX Pros

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.

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:42
interesting

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:43

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:43

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:43

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:43

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:43

Marco thumbnail thumb Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

2017-09-07 10:46
for everyone

headshot smaller thumb Coffee Talk: DAX Pros

2017-09-07 10:46

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:46
to eliminate EARLIER

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

2017-09-07 10:47

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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 thumb Coffee Talk: DAX Pros

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.

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:50
like with M

Marco thumbnail thumb Coffee Talk: DAX Pros

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.

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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.

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:51
ohhh, nooo

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:51

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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.

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:53
in May 2016

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:53
I hate them

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:53
with a passion

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:53

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

2017-09-07 10:55

Marco thumbnail thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 10:55
for all users

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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 thumb Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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 thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

DAX Love

Marco thumbnail thumb Coffee Talk: DAX Pros

2017-09-07 11:01
I love DAX

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 11:02
but for me

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 11:02

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

2017-09-07 11:03

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 11:03
Definitely

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 11:05
rows and columns

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 11:05
cell by cell

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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.”

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 11:06
they are impressed

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 11:07
specifically

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

2017-09-07 11:08

headshot smaller thumb Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

2017-09-07 11:10

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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 thumb Coffee Talk: DAX Pros

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 thumb Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

Wrapping Up

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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 thumb Coffee Talk: DAX Pros

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…

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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?

Marco thumbnail thumb Coffee Talk: DAX Pros

2017-09-07 11:19

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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 thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 11:21

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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?”

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

2017-09-07 11:22
i love that

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 11:22

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 11:22
the limits

headshot smaller thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 11:22
in my training

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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?

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

2017-09-07 11:25

headshot smaller thumb Coffee Talk: DAX Pros

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 thumb Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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.

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

2017-09-07 11:29
Closing comments?

headshot smaller thumb Coffee Talk: DAX Pros

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.

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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.

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 11:32

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

headshot smaller thumb Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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 thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 11:34

headshot smaller thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

62532670500 bef90a8385687e039629 72 Coffee Talk: DAX Pros

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 thumb Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

2017-09-07 11:35
See you guys

Marco thumbnail thumb Coffee Talk: DAX Pros

2017-09-07 11:35
Bye Austin

Marco thumbnail thumb Coffee Talk: DAX Pros

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

Marco thumbnail thumb Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

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

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 11:37
3rd quarter

173926111396 a13d4b8f9bb999c04a4c 72 Coffee Talk: DAX Pros

2017-09-07 11:37
Looking good

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

Let’s block ads! (Why?)

PowerPivotPro

Jan 23rd The Powerful Interactions between Excel and Power BI by Tristan Malherbe

One of the most popular presenters from the Power BI World Tour will be presenting this weeks webinar!

 

The Powerful Interactions between Excel & Power BI by Tristan Malherbe

Abstract

We will see all the interactions between Excel and Power BI (Power BI in Excel, Excel connector, Power BI Publisher for Excel, Analyse in Excel, Export to Excel, push Data model from Excel to Power BI Service) with practical examples and relevant scenarios.

 

When: 1/23/2018 10AM PST

Where: https://www.youtube.com/watch?v=sM8xkNyZaTU

 

About the presenter

AAEAAQAAAAAAAAPLAAAAJGEyZWUzNzMxLWI5YmMtNDgwMy05ZWYwLTAxMjgyMzgzY2FkMg Jan 23rd The Powerful Interactions between Excel and Power BI by Tristan Malherbe

Tristan Malherbe, a Power Microsoft MVP, a renowned Business Intelligence  Consultant at AZEO and President of the Power BI User Group in France.  As a PUG leader for Power BI in France, Tristan’s passion for the product comes from his interest in solving problems and creating user-friendly applications to support decision making, to analyse or visualise data.

Let’s block ads! (Why?)

Microsoft Power BI Blog | Microsoft Power BI

Power BI Developer community January update

Hi everyone, after a short break for the holidays we are back with the latest updates for Power BI Developer community. Don’t forget to check out the November blog post, if you haven’t done so already.

b93673cf 2d6b 4204 8b53 42192182190a Power BI Developer community January update

Here is the list of January updates for Power BI Embedded

Embed capabilities

- Single visual embedding

- Custom layout

- Tile embedding enhancements

- Phased loading of embedded content

Automation & life-cycle management

- Update report

- Tile & Dashboard RLS support

New capacities in Azure

- ARM PowerShell

Single visual embedding

This month we are very excited to release this highly requested feature that supports visual embedding. From now on, you can give your users more than a general ‘Analytics’ page. By embedding visuals into your application, you can get in-page analytics where only the most relevant and insightful data resides next to your user’s day-to-day actions, making data-driven decisions easier than ever.

The fully flexible API gives you the capability to choose which visual to show for each user, or switch visuals in the same iframe during the user’s session. You can optimize the size and layout of each visual at any time using the new ‘custom layout’ feature, which will be further discussed in this post.

Unlike tile embedding, in order to embed a visual you specify the report and report page for the visual. From the list of visuals in a page, you define which visuals will be hidden and which will be visible. This methodology has few advantages:

  1. Switching visuals- by switching visuals on the same report, you do not need to generate a new embed token. Just switch the visibility definition of the visuals and you will immediately show different visuals that was already rendered.
  2. Displaying multiple visuals- Why settle for a single visual, if you need more than one to gain insights? As you define the visibility of each visual, you can choose to show multiple visuals on the same iframe and define in real time the size and position of each one. By showing multiple visuals, you also enjoy interactivity and cross- filtering between visuals, just like a regular report.

You can explore the new API through our Live demo tool, by choosing ‘Sample Report Visual’. You can read more on Single visual embedding in our documentation.

Custom layout

Custom layout allows you to set and change the size of a report page layout, or control the size, position and visibility of a report visual.

1. Page layout- When building a report page through Power BI Desktop, you configure the layout using two fields: page size type and page layout.

db86076d 239d 4c9c 853c 93a3a3925791 Power BI Developer community January update                                 1b21eb5a 5983 4a3b a247 a65c6c86ea3a Power BI Developer community January update

By default, each time you embed a report, it uses the configuration made in Power BI Desktop. With Custom Layout, you can dynamically set the definitions to optimize the report layout inside your application page. You can change the layout during the session, based on specific events such as content change in the application page or browser resizing.

2. Visual layout- This feature also supports single visual embedding. Through custom layout, you define which of the visuals you wish to display and specify their size and position on the canvas. Using this capability, you have full configuration control on the appearance of the visuals on the canvas dynamically during the user’s session.

For example, assume that your application’s customer has different departments. Instead of creating copies of the same report or report pages for each department and make the modifications, you can show the same report page and expose only the relevant visuals to that user. Custom layout allows you to optimize the position of each visual so that the experience will be seamless.

Another example allows users to choose which visuals they want to follow and create their own customized view of the report. Learn more on Custom layout.

An example and code snippet can be found in the Live demo tool. To use the live demo tool for custom layout, follow these steps:

  • Choose ‘Sample Report’
  • Click ‘Run’
  • Go to ‘Interact’ tab
  • Scroll and choose ‘Apply custom layout’
  • Click ‘Run’ again

Phased loading of embedded content

The loading of an embedded artifact is done using ‘embed’ call, containing an embed configuration object and the

element that contains the iframe. Once called, the embedded object is loaded in front of the end-user, and any interaction with the object is done while shown to the end-user.

To improve the end-user experience and provide more tools for developers, we have extended this flow with more JavaScript calls that add phases to the embedding process:

  1. Preload- the ‘powerbi.preload()’ call uses the browser’s cache and download scripts prior to showing the embedded object itself. This can be proven useful for applications hosting several embedded reports. A ‘ready’ event will be fired once the preload is completed. You can read more details on our previous blog post.
  2. Load metadata- the ‘powerbi.load()’ call helps retrieve info on the embedded object, and dynamically changes the settings or the

    element in the background, before the object is displayed to the end- user. For example, you can use this function to get pages and then decide which page to show the end-user. Or, you can use ‘get visuals’ and then decide which visuals you wish to show or hide to the users. If you used this function, you need to call the ‘render()’ function to show the embedded object. Once the load is completed, a ‘loaded’ event will be fired.

  3. Render object- The final call, in case you implemented the ‘powerbi.load()’ function, is ‘render()’. The render completes the last actions needed to render and show the embedded object to the end- user. A ‘rendered’ event will be fired once the render is completed.

While the ‘load’ and ‘render’ functions must be used together, ‘preload’ is independent of the prior two. You can use ‘embed’ function to show the embedded object after ‘preload’, as it was done until now. One can also utilize the full phased procedure by calling ‘preload’, ‘load’ & ‘render’ in this respective order.

Tile embedding enhancements

We added support for more tile types that can be embedded. Here are the additional tile types:

  • live page tiles
  • Custom visual tiles
  • R visual tiles
  • live stream tiles
  • image tiles
  • video tiles
  • web content tiles
  • Q&A tiles
  • Visio tiles

Read more on tile embedding.

Update report

After releasing the ‘Clone report’ and ‘Clone Tile & Dashboard’ APIs, which allowed to copy content so that multiple customers can get analytics on their data with minimal API calls, we now release an API to support updating reports.

As an independent software vendor (ISV) with multiple customers, or an organization with different departments, you can hold a copy of the report connected to different data for each of them. We highly recommend saving analytical content for different customers or departments in separate workspaces.

Now let’s assume you have 20 customers (or 20 departments), you clone and rebind the source report 20 times to give each customer his own view of the data in his dedicated workspace. Now you wish to change the source report, fix some small bugs and add some customer requirements. Using the new API, you can automatically update all reports that were created from the same source report, while keeping the original report ID used for embedding and sharing. This means no effect on your code when you wish to improve your reports.

The benefits of this operation go beyond cloned/imported reports. You can update a target report by providing any source report as long as the dataset schema complies with the update. The dataset itself in the target report will not change, and the updated report will continue to be linked to the previous dataset.

Note that an update operation changes the entire content of the report. Any visual/ page that is not on the source report will be deleted once the operation takes place. We highly recommend as a best practice to keep the source and target IDs of all objects you are cloning, so that the ‘Update’ operation can be done easily and with minimal errors.

Tile & Dashboard RLS support

We now support row-level security (RLS) for Tiles and Dashboards embedding. The implementation is similar to Report RLS and is done through the embedToken. The API accepts both multiple datasets and multiple identities to support dashboards containing multiple tiles that are connected to different reports.

Here are some examples of how to change the ‘identities’ Json when calling a REST API with multiple roles or datasets:

1. Using ‘effectiveIdentities’ to generate a token which specifies using ‘userA’ with role ‘Role1’ to access a dataset, and ‘userB’ with role ‘Role2’ to access a different dataset:

var effectiveIdentities = new []

{

  new EffectiveIdentity

  {

    Username = "userA@contoso.com",

    Roles = new[]

     {

​       "Role1"

​     },

     Datasets = new[]

     {

       "1ee99c99-6475-4b62-9a55-015018cee352"

     }

  },

  new EffectiveIdentity

  {

     Username = "userB@contoso.com",

     Roles = new []

     {

       "Role2"

     },

     Datasets = new []

     {

       "c1e3dfcc-1c20-4e1d-bd35-02d2e5512480"

     }

  }

};

2. Using ‘effectiveIdentities’ to generate a token which specifies using ‘userA’ with role ‘Role1’ with 2 different datasets:

var effectiveIdentities = new []

  {

   new EffectiveIdentity

   {

     Username = "userA@contoso.com",

     Roles = new []

     {

       "Role1"

     },

     Datasets = new []

     {

       "1ee99c99-6475-4b62-9a55-015018cee352",

       "c1e3dfcc-1c20-4e1d-bd35-02d2e5512480"

     }

   }

};

Generating token for embedding a dashboard with ‘effectiveIdentities’ as specified in option #1 or #2:

var powerBIclient = new PowerBIClient(credentials);

var requestParameters = new GenerateTokenRequest(accessLevel: "View", identities: effectiveIdentities);

var token = await powerBIclient.Dashboards.GenerateTokenInGroupAsync(groupId, dashboardKey, requestParameters);

Azure Resource Manager (ARM) API using PowerShell

In November we released the ARM API to automatically manage Power BI Embedded capacities (A SKUs). This now can be done through PowerShell commands. You can find more information on ARM API in our previous blog, or through documentation. The following commands have support in PowerShell:

Power BI Embedded PowerShell cmdlets are in release 5.1.1 of Azure PowerShell.

If you are puzzled or run into few issues, be sure to check our resources that can help you get through:

  • ‘Troubleshooting’ doc to help you get by all the obstacles on your way for building a great app.
  • FAQ doc to answer all your question regarding the Product, licensing and Azure offering.
  • Community forum for developers, to seek answers or ask any other questions you have.

 

API 1.10

API 1.10 is available on GitHub and adds the following capabilities:

Authentication with Azure Active Directory (AAD)

Custom visuals can call this API to get an AAD token that authenticates them, to be used with other AAD based services for Single Sign-On (SSO). This enables developers to build visuals that can be authenticated through SSO with other services. For this to work it is required that:

  • The service is registered with AAD, to approve the token generation
  • The service is registered in Power BI, to approve the visual to go out to request the token

If you want to use this API, contact the custom visuals support team at pbicvsupport@microsoft.com for details

Localize anything with LocalizationManager

API 1.6.2 introduces the localization of the formatting pane through the capabilities.json file. Now, developers can use the localized strings inside their TypeScript code, and practically localize any string that is being used inside the code, like tooltips, chart item names (e.g. ‘axis’, ‘legend’ , etc.). See the documentation for more details.

As always, feel free to use all the communication channels at your disposal to connect with our team, share your thoughts and ask questions:

That’s all for this post. We hope you found it useful. Please continue sending us your feedback, it’s very important for us. Have an amazing feature in mind? please share it and vote in our Power BI Embedded Ideas forum, or our Custom Visuals Ideas forum.

Let’s block ads! (Why?)

Microsoft Power BI Blog | Microsoft Power BI

Balanced Scorecards in Power BI

By Greg Koehler, Program Manager at Microsoft

“IMAGINE ENTERING THE COCKPIT of a modern jet airplane and seeing only a single instrument there.”
Robert S. Kaplan, The Balanced Scorecard: Translating Strategy into Action

As a business intelligence (BI) professional at Microsoft, I’m often asked how to measure success for a given product, service, team, or organization.  My usual answer is there’s not just one gauge. True success is broader and more holistic than a single metric can adequately represent.  While I believe that everything can be measured, I advocate that only certain, key measurements be used to most effectively drive alignment across large organizations.  This helps guide thousands of employees to focus on centralized strategy and goals over both the short-term and long-term.

“The Balanced Scorecard,” by Robert Kaplan and David Norton, was published by Harvard Business School Press in 1996.   The book recommends a management approach based on key performance indicators (KPIs) tracking of strategic progress toward central goals across four perspectives, including financial measures, customer knowledge, internal processes, and learning/growth.   Not long after publication, the management practices advocated by Norton/Kaplan began to be adopted by large businesses, including many Fortune 500 firms.  According to past surveys from consultants including Gartner, Bain and a recent survey from 2GC, most large businesses today use a balanced scorecard approach.  Today, we still see business information firms, such as the Wall Street Journal (WSJ) and the Drucker Institute, apply similar methodology when evaluating the performance of top companies (see the WSJ’s latest “Management Top 250”). My team at Microsoft, called CDnA for Customer Data and Analytics, is no exception.  We measure a variety of key metrics, both financial and non-financial, including customer usage and satisfaction of Microsoft products and services.

While the Kaplan/Norton books (including sequels) may help enterprises and their MBA employees with the conceptual framework for translating strategy into a plan of action, the information technology is left up to each business to figure out.  If you’ve been working for a while in business intelligence and analytics, you probably already know about some of the many challenges of implementing a measurement system that supports entirely different types of KPIs.  Typically, financials are kept in completely different backend systems than customer usage data, which, if available, is still quite different from operational telemetry or sentiments expressed from internal employee surveys.   The backend challenges are often addressed with a combination of aggregation layers and/or a middle tier to normalize metadata (such as for compliance standard geography and time dimensions). 

After the backend data is aligned, an equally daunting challenge is the front end – the production and presentation of the scorecard.   For years, and of course being at Microsoft, many people throughout the company naturally turned to Microsoft Excel and elaborate data-driven spreadsheets to provide such views.  The flexibility and power of Excel was, and is still, difficult to beat.  Along with the convenience of Excel were some inconveniences: with offline, disconnected spreadsheets being e-mailed around, data was often stale and multiple files often caused confusion as to the true version.  Also, one could only drill so far into a single spreadsheet before running into the storage limits of Excel, especially when working outside the corporate network. 

More recently, and especially with the rapid adoption of Power BI dashboards and reports by so many teams within Microsoft, my own team started to use Power BI visuals connected to on-premises SQL Analysis Services tabular models via the Power BI On-Premise Gateway to present balanced scorecard views for executive reporting and strategic management.  We started by working with the Power BI team to develop a custom visual, called Power KPI, which we decided to make available to the public for free in the AppSource store.   Power KPI presents detailed historical trend data and target comparisons for a single KPI. 

PowerKPI Balanced Scorecards in Power BI
(above, the Power KPI custom visual)

The visuals may also be sized in a compact style, which makes it great for showcasing multiple KPIs on the same dashboard or report.

PowerKPI Dash Sample Balanced Scorecards in Power BI
(above, multiple Power KPI custom visuals are pinned to a Power BI dashboard)

On the backend, we source multiple Analysis Services tabular models via the Power BI on-premises data gateway and link to a single dashboard by pinning visuals in the Power BI Service.  End users access the latest KPIs by using a web browser or the Power BI app on any device, from anywhere with an internet connection.

Power KPI Matrix

While the Power KPI visual has been a necessary building block for us in the presentation of KPIs, by itself it doesn’t provide a scorecard layout.  Some users, including executives, really wanted a tabular, scorecard style of KPIs.  For this, we developed a second custom visual called Power KPI Matrix.  Power KPI Matrix supports an unlimited number of KPIs in a single visual, along with optional categorizations, images, and sparklines.  With the latest version, 2.0, it also includes an interactive pop-out of the full Power KPI chart within the visual for a given selected cell.  While we developed it primarily for our own team and purpose, it has also been made available for free to the public.

PowerKPIMatrix Balanced Scorecards in Power BI
(above, the Power KPI Matrix custom visual)

Richa Yadav, a program manager with the CDnA team, has recently produced a Power BI dashboard to drive insights into Microsoft Office performance and customer usage. This dashboard includes 43 different metrics that all contribute to Microsoft’s business and decision making.  As Richa explains, “with the increase in number of metrics each year, it was extremely important to have a view that not only ranks the KPIs but also includes the historic trends (actuals versus targets) in a single glance. Power KPI Matrix comes in handy as it serves as a balanced scorecard which perfectly captures our entire list of metrics in one single visual and enables drilling further into trendlines without moving away from the visual — a very unique feature.”

Below is a screenshot of the drilldown feature mentioned by Richa:
PowerKPIMatrix PopOut Balanced Scorecards in Power BI

Beyond balanced scorecards, Kaplan and Norton have also focused on strategy map visuals, which are akin to flowcharts that tie together strategy with measurement.   For those interested, the Visio visual (preview) custom visual offers a great option for lighting up strategy maps based on data.

Visio Preview Balanced Scorecards in Power BI
(above, the Visio preview custom visual with shapes linked to live data measurements)

The custom visuals shown here also work well within the mobile Power BI applications.  Using Android or iOS mobile devices, end users are easily able to look up the latest information and interact with the data, at any time, from anywhere.

Power KPI Matrix in a Mobile Dashboard (Android): clear Balanced Scorecards in Power BI In-Focus Mode with Pop-Out Power KPI Visual (Android):
PowerKPIMatrix Mobile1 Balanced Scorecards in Power BI clear Balanced Scorecards in Power BI PowerKPIMatrix Mobile2 Balanced Scorecards in Power BI

With Power BI and just a few powerful custom visuals, both large enterprises and smaller companies are now poised to achieve strategic alignment at a broad, pervasive level throughout the organization that Norton and Kaplan might only have dreamed of when they published “The Balanced Scorecard.”  There is still some pesky backend data integration work, but that’s a story for another day. 

Sources

Kaplan, Robert and Norton, David.  The Balanced Scorecard, Harvard Business School Press, 1996.

Keeping Score, The Drucker Institute. https://www.drucker.institute/thedx/keeping-score/

Management Top 250, WSJ. http://graphics.wsj.com/image-grid/management-top-250/

Methodology for the Management Top 250 Company Rankings, WSJ. https://www.wsj.com/articles/methodology-for-the-management-top-250-company-rankings-1512482700?mg=prod/accounts-wsj

Balanced Scorecard Basics, Balanced Scorecard Institute.  http://www.balancedscorecard.org/BSC-Basics/About-the-Balanced-Scorecard

Related:

How Microsoft Executives are Using Power BI, Microsoft Power BI Blog. https://powerbi.microsoft.com/en-us/blog/here-s-how-microsoft-executives-are-using-power-bi/

Power KPI custom Visuals:

Power KPI, AppSource.  https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381083?src=office&tab=Overview

Power KPI Matrix, AppSource. https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381299?src=office&tab=Overview

Visio visual (preview), AppSource. https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381132?src=office&tab=Overview

Let’s block ads! (Why?)

Microsoft Power BI Blog | Microsoft Power BI

1/18 Webinar: Using PowerApps and Flow to create Line of Business “portals” by Vishwas Lele

This webinar is designed to show you how to more easily create PowerApps applications and take advantage of the recently introduced PowerApps custom visual for Power BI

Using PowerApps and Flow to create Line of Business Enterprise “portals” by Vishwas Lele

 

Vishwas will showcase a PowerApp application that is essentially a “portal” for existing Line of Business Enterprise Applications (inventory, contracts etc.) and Services ( Dynamics, O365, DropBox etc. )Through the use of PowerApps features like the out of the box connectors, integration with Flow and mobile enablement, learn how easy it is to build an app for the information workers that allows them to  have all the information in one location and on a device of their choice. 

When 1/18/2018 10AM

Where: https://www.youtube.com/watch?v=eSMAAFHK44c

About Vishwas Lele

vishwas lele v1 1/18 Webinar: Using PowerApps and Flow to create Line of Business “portals” by Vishwas Lele

Vishwas Lele serves as Chief Technology Officer at Applied Information Sciences, Inc. Mr. Lele is responsible for assisting organizations in envisioning, designing, and implementing enterprise solutions. Mr. Lele brings close to 24 years of experience and thought leadership to his position, and has been at AIS for 18 years. A noted industry speaker and author, Mr. Lele serves as Microsoft Regional Director for the Washington, D.C. area and is a member of Windows Azure Insiders group. Additionally, Mr. Lele received an MVP (Most Valuable Professional) for Solution Architecture.

Let’s block ads! (Why?)

Microsoft Power BI Blog | Microsoft Power BI

Journey to PowerPivotPro: Reid Havens

Buckle in folks, this is going to be a long and engaging article. As we enter 2018, I realized that I’m approaching the one-year anniversary of my first blog post here at PowerPivotPro! So many things have happened since then. I’ve traveled across the US as a BI Trainer, co-developed with some of the world’s biggest companies, and a few months ago was given the position of EDITOR here at P3. Given all that’s happened I thought it appropriate to take a step back, reflect, and share my story of how I got here.

You see, when I was first hired here at P3 and shared my story with others, I was told that someday I NEEDED to blog about it. While I’m not one to toot my own horn, I’ve been told my career path was very non-typical (to say the least)! So here I am today, finally ready to share that journey with you, our readers. Because here at P3 we want to be more than just bloggers, we want to give a personality and face behind the articles you read. In fact, my goal is for this post to be the first of MANY! With the hopes that our core authors will all eventually get the opportunity to share their stories with you. So, sit back, enjoy your coffee, and learn a little more about how I got here.

road sign thumb 6 Journey to PowerPivotPro: Reid HavensStepping back into my way back machine, let’s travel back to the early 2000’s! Young me was in high school, and eager to work in the tech industry. By the way, you’re not getting any photos of me from then…let’s just say frosted tips was a BAD lifestyle choice… Anyways, I threw myself into computer hardware, and even got an A+ Certification by 16. However, I got disillusioned by the monotony of software coding and COMPLETELY changed directions after High School.

Instead, I invested in Psychology for my B.A. Thinking psychology was the career path for me. Turns out it’s really REALLY hard to get a soft skills job in 2009 WITHOUT EXPERIENCE. Worst. Time. Ever… to enter the job market…yeah. So instead, I ended up working as a behavioral instructor with autistic children for about four years, random right? During my time there I invested in a Master’s Degree in Organizational Development, hoping to get a leg up in the business industry.

After graduating, and after probably spending a few hundred hours sending my resume to (I swear!) nearly every analytics company that existed in the greater Northwest, I got a response back from MICROSOFT. They wanted to fly me down to Texas for an interview for an Account Manager Position. Not only was this the FIRST time I’d ever been flown to an interview, it was MICROSOFT! Needless to say, I was thrilled! However, even though my interviewers LOVED my personality, and even thought I’d be a great fit with the culture there…I didn’t have enough technical skills. Yup, that’s right, I didn’t get the job.

Not all was lost though! I got one bit of sage wisdom from an interviewer down there. Go back and get a technical certification in something. So, I did! Some thorough research into growing industries, and data showed me that analytics and business intelligence was one of the fastest growing fields. So, I began looking for certifications related to that. I eventually applied for a business intelligence & database certification to be exact, at Bellevue College.

loves data thumb 2 Journey to PowerPivotPro: Reid HavensLet me tell you, I threw my body and mind into that certification, and by god did I come out the other end with flying colors. In fact, my capstone professor even wrote me a glowing recommendation. She stated that I was one of the top three students she ever had (trust me, this plays out later). So here I am, with a freshly printed certification, and bright eyed and bushy tailed for a new job opportunity. Of course, I did what any job applicant does, check all the online job websites for postings in the area.

You know where this is going though right? I very quickly exhausted EVERY SINGLE job posting in the area, and NO bites. A couple nibbles…but no bites. Do I give up though? Hell no! Fresh out of online job postings, I instead began googling analytics and business intelligence companies in my area. That’s right, I cold emailed about 30 companies that looked like they could use someone like me. Finally, after a few dozen emails with NO reply, one responded!

video training p3 thumb Journey to PowerPivotPro: Reid HavensThey brought me in for an interview, an interview that lasted FOUR HOURS! I got the hint that the interviewer (company owner) really liked me, and he told me he wanted to bring me on board. I was given a learning role, where I’d get hands on training and resources to quickly ramp up to a deployable consultant. Now I want to point out RIGHT NOW, that until I got hired here, I hadn’t used Excel or PowerPivot AT ALL. Like zero, nil, zilch, none. So I’m learning completely on the fly, and quickly learn all I can from YouTube videos and Googling blog posts. Now at this time, almost every time I Googled a PowerPivot question, PowerPivotPro and Rob’s posts would show up!

So I convinced my boss to buy Rob’s video training for me, and I threw myself headfirst into the content. I quickly absorbed everything in those videos…taking copious amounts of notes along the way. But I wanted more! I craved DAX and Data Modeling like a fat kid craves candy. So what do I do? I go back to Bellevue College and take a class on PowerPivot. Now low and behold…my capstone professor from my certification was teaching this class (told you this would come back).

Almost IMMEDIATELY I realized I knew all the material being taught already, I even helped out the instructor a few times when she got stuck! Fast forward about two weeks after the class finished, I get an email from her. In this email she said that due to multiple projects and responsibilities, she wouldn’t have time to teach the class next quarter, and asked if I would like to step in! Yes, you read that right. Let me pause right now, and remind you that I’ve been using PowerPivot for only A FEW MONTHS! Yet I’ve somehow been given the opportunity to teach it at a college…

The idea of teaching TERRIFIED me. However, I knew that having instructor on my resume would give me a huge boost, so I swallowed my fear and said yes. Now, before the class even started, I spent an INSANE amount of hours learning and improving the class material. I memorized it, practiced what I’d say, added content, etc… All in hopes I wouldn’t be a nervous wreck come class day. While it did prepare me for class, it did very little to dispel my fears. Come the day of the class, I was sweating bullets by the time class started…

By the time that first class ended I was positive it went HORRIBLY. On the drive home I was even thinking how I’d write a resignation email, after this class was done. I’d thank them for the opportunity, but that I wouldn’t want to do another class after this. THANKFULLY each class at Bellevue College includes an instructor evaluation on day one. The evaluations included a one to five scale review of various aspects of the class, plus written feedback. The feedback is typically released back to the instructor the next day, so I went to bed that night dreading what the results would be.

teacher thumb 5 Journey to PowerPivotPro: Reid HavensBy the time I opened the email the next morning with my evaluation results, I was still convinced that it would be terrible. To my surprise, the reviews were almost all fours and fives…OUT OF FIVE. I honestly had to re-read the evaluation multiple times, not believing how good those reviews were. Not only were my fears hyperbolic, they apparently really LOVED the class. I later found out that I received one of the highest marks for a first-time instructor ever!

Since that day I’ve come to LOVE teaching, and honestly, it’s now the most rewarding thing I do. If you’d asked me even a year before this, would you ever teach? My answer honestly would have been HELL NO! Funny sometimes how life throws you random opportunities, especially one’s you end up being really good at!

So the entire time I’m working as an analyst and teaching, I’m also attending a local Excel Meetup Group. At one point shortly after I start teaching at Bellevue college, Rob does a remote presentation during one of the monthly meetups. After the presentation there’s a general Q&A section at the end. Of course me being me, I ask for the mic. I tell Rob that I don’t have a question, but just that I wanted to thank him for the blog, and the online course he made, because it literally helped me get a job as an instructor.

Rob mentioned how AWESOME that was, and that’s why he loved doing the blog. He mentioned a few other words of affirmation, and then I handed the mic off to the next person. Nothing else came of it that night, but this moment, this exact moment, was the start of the next phase of my career. I’d just lit a spark in the room that day, I just didn’t know it yet!

P3 Ladder thumb 5 Journey to PowerPivotPro: Reid HavensFast forward another six months or so. I noticed one day that there was a job posting on the PowerPivotPro website, asking for Principal Consultant applications. Now at this point I hadn’t personally met ANYONE from P3, and in my mind, they were the Jedi Masters of the BI world. So I ALMOST didn’t even apply, but a voice in the back of my head kept telling me…DO IT! So I applied, and even got a response back from Rob, containing a DAX test.

Let me tell you right now, that test is HARD. For my skills at that time, capitol H A R D. I went through a few iterations with Rob, and FINALLY got to an acceptable answer. Never mind you, a few grey hairs from how much thinking that test required. Unfortunately, because it took a few attempts, Rob was hesitant to hire me out right. Not all was lost though! I once again found myself doing something no one else had done (at that point).

Rob told me that every other test taker either A) Ninja’d the right answer their first go, or B) Completely failed! Until I came along, NO ONE had ever stumbled their way success like I did. Which is why I didn’t get a NO, instead I was told he wanted to think about my situation a bit. Still a damn good result from an application I almost DIDN’T submit, and an overall win in my book. This is now the second spark I’ve lit without knowing it! These sparks would collide in the most wondrous way later that fall.

rocket ship thumb 1 Journey to PowerPivotPro: Reid HavensLet’s hop in the Delorean, and shoot forward another two months. I’m at one of the monthly Excel User Groups I regularly attend, with a presentation being given by a local principal consultant for PowerPivotPro. Being the extraverted networker I am, I go up and talk with him. As we’re talking I mention who I am, that I teach, etc… All of a sudden he asks me if I was that same person from last year’s meetup. The one where I told Rob about how the blog and training helped get me teaching position.

Apparently he was there, heard my story, and was AMAZED by it. He’d been wondering who I was ever since. He’d also shared that story with a few other people since then, as an example of the quality and successRob’s video training can provide people. So we schedule some time to get some coffee later that week. About half way through having coffee I realize these questions seem very interview like, and that it seems like he’s vetting me as a potential hire. I mention that I’d applied earlier that year with Rob, and explained Rob’s interesting scenario with me. He told me to send over the test I’d given to Rob, and that we’d go from there.

All the pieces then fell into place! The coffee talk went very well and I’d given off a fantastic impression, so they now had a personality to put to the DAX test I’d submitted. That was now enough to convince Rob bring me on as a Principal Consultant. It was like a series of fortunate events, all leading to an amazing career opportunity. The rest as they say, is history. It’s been a rocket ship of a roller coaster ever since.

teacher with screen no tie thumb 2 Journey to PowerPivotPro: Reid HavensThat was about a year and a half ago now. Since then that rocket ship hasn’t slowed down one bit! Pretty quickly after getting hired, I voiced an interest in writing for the blog, and sent my draft to Rob. His feedback was overwhelminglypositive, even including a few colorful words I probably shouldn’t write in this post. Turned out I had a knack for writing, that was very inline with the P3 style! That article can be found here. Before I knew it, I was churning out a post or so a month, and quickly became a core blog contributor.

2017 was a year with so many amazing events happening. I’ve traveled all over the country giving training’s under the P3 name, which has been so rewarding. My writing style, contributions, and continued interest in the blog eventually landed me an offer to become the editor of the blog here at P3. I took the offer without missing a beat, and have taken on that role in full stride.

The opportunities didn’t stop there! Because of my experience at Bellevue College I had a colleague from Microsoft recommend me as potential course developer at the University of Washington. So in general, you may have noticed that I’m often a yes person. I take opportunities when they come my way, even if it’s scary or hard. Same thing applies here. I took on the job to develop a data visualization course for UW, knowing full well it might bury me with work. Needless to say, it did. It was a ROUGH six months of development. But I stuck with it, and produced some awesome content.

Part of the course development required me to record about 15 HOURS of online content for the university. This was a very new experience for me! I’m the first to say I really HATE photos or videos of me, ESPECIALLY hearing my own voice. So I wasn’t sure how the recordings would go. Oddly enough, almost everyone involved in the recordings asked if I’d done it before, because apparently I appeared very relaxed on camera. So thankfully it seemed like I was a natural, and that my personality translated well on camera.

thinking thumb 2 Journey to PowerPivotPro: Reid HavensOn top of everything else, I’ve even been nominated for an MVP Status at MICROSOFT this year…so fingers crossed! Looking back, I’m AMAZED at how much progress I’ve made in the last four years. It’s been such a mixture of opportunities, tenacity, and perseverance that has brought me here. I’ll be the first to say how grateful I am to have had these opportunities.

I’m INCREDIBLY exited to see what 2018 has in store. If 2017 was any indication, this year will continue to blast through the atmosphere with opportunities. I want to also take this moment to point out what an amazing company, and team PowerPivotPro is. Besides the opportunity for travel and blog writing, the sheer amount of brains and knowledge within our company is amazing. At the touch of a keyboard, any one of our consultants can reach out and ask each other questions. This has proven invaluable for learning. Here at P3 it feels like mixture of colleagues and family, not just having to help each other, but wanting to.

With that being said, that wraps up most of my journey through today. It’s been such an incredible ride, and with some truly incredible people. I’m here with you readers for the long haul. So expect many many more posts from us, and me. Thanks for sticking through with this post as I shared my story, and I’ll see you in my next article. wlEmoticon smile 2 Journey to PowerPivotPro: Reid Havens

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

Let’s block ads! (Why?)

PowerPivotPro

Microsoft Deep Drive Track at the Data and BI Summit April 22-26

Join Business Analysts, Data Professionals & Power BI Users at the inaugural Data & BI Summit, located in Dublin, Ireland 24-26 April 2018 at the Convention Centre Dublin.

Data BI 18 Blog Header3 Microsoft Deep Drive Track at the Data and BI Summit April 22 26

 

In addition to all the amazing luminaries in the Business Intelligence space the Microsoft Team will be at the BI Summit in full force!

Check out the members of the Power BI team already confirmed and their deep dive sessions being delivered specifically for the Data & BI Summit.

  1. Shape your data into a data model with Power BI Desktop by Will Thompson
  2. Enhance your model with Power BI Desktop presented by Will Thompson
  3. Advanced data visualization with Power BI Desktop presented by Amanda Cofsky
  4. Story telling with Power BI Desktop presented by Amanda Cofsky
  5. Power BI administration & governance presented by Charles Sterling, Nikhil Gaekwad and Kay Unkroth
  6. Advanced Power BI Embedded development topics by Alon Baram
  7. Security deep dive presented by Kasper de Jonge

 

Shape your data into a data model with Power BI Desktop by Will Thompson   

At high level this session will cover, using Power BI Desktop to import and shape data from a variety of different sources. Specifically, you will be able to: Understand the Power BI Desktop data model, the components of effective schemas, gain an understanding of the Power Query M language, Import data from a variety of sources (including XLS and CSV files), Create queries using toolbar navigations and Advanced Editor, Understand parameters and finally organize queries using folders.

Enhance your Model with Power BI Desktop presented by Will Thompson   

In this session conference favorite and Microsoft Power BI Program Manager Will Thompson will walk through parsing data modeling formulas, using DAX to create calculations, consequences of data model design decisions, concepts of calculated columns and measures with particular focus on patterns & CALCULATE

Advanced Data Visualization with Power BI Desktop presented by Amanda Cofsky

By the end of this course, you gain a better understanding of the components that influence communicating data visually. Specifically gain familiarity with Power BI report layouts and structure the agile process to creating Power BI data visualizations. Understand the art behind visualizations., understand implications behind choosing the right charts, the impact of color, shape and size and finally the use of Power BI custom visuals.

Story Telling with Power BI Desktop presented by Amanda Cofsky

Join us for this fun interactive session by Amanda Cofsky, Program Manager for the Power BI team at Redmond. Learn how to use bookmarks in Power BI to communicate insights in your data. Be one of the first to learn about and use this brand-new feature that shipped in the October release of Power BI to tell stories with your data and help your users navigate your dashboards and reports. Amanda will finish with real world examples and bookmark tips created by Miranda Li, Data Visualization expert from Customer Data and Analytics team at Microsoft.

Power BI Administration & Governance presented by Charles Sterling, Nikhil Gaekwad and Kay Unkroth

Meant as a hands-on and interactive compliment to Nikhil’s administration session: “Enterprise BI Deployments and Governance with Power BI” where Nikhil covered enterprise-wide reporting deployments and providing structure to self-service BI activities within teams.  In this session you will use the tools for publishing and managing your BI assets. Topics being covered are using capacities in Power BI, safely sharing Power BI assets and finally using the Power BI audit log to optimize your Power BI Usage.

Advanced Power BI Embedded Development Topics by Alon Baram

In this session senior product manager Alon Baram will dig into the Power BI Embedding topics such as how to implement Row Level Security, credential handling, and application lifecycle integration such as automating workspace cloning/creation.  Alon’s session will end with discussion of the road map moving forward. (Editors Note: The development team may send out another of the PMs).

Security Deep Dive presented by Kasper de Jonge

One hot topic with Power BI is security, in this deep dive session we will look at all the aspects of security of Power BI, from users, logging to where and how your data is stored and we even look at how to leverage additional Azure services to secure it even more.

 

The Presenters:

About Alon Baram

Senior Product Manager at Microsoft.  Seasoned product manager with substantial inbound and outbound skills.
A profound catalyst, possessing excellent matrix management and execution capabilities.
Vast experience in driving various products throughout their life cycle stages, from inception to successful launch.
Expertise in the areas of cloud-based enterprise services and Telecom Value Added Services (VAS).

About Amanda Cofsky

VCB8D8om 400x400 Microsoft Deep Drive Track at the Data and BI Summit April 22 26

Program Manager, Power BI Microsoft.  This proud-to-be an Aggie and the most popular blogger on the Power BI team started Microsoft in 2013.  In her first role, she shepherded one of Microsoft’s first BI tools Power View, a data visualization tool built into Excel where her main focus was chart formatting and design.  From there Amanda helped create a team dedicated to BI tooling: Microsoft’s premier data analytics Power BI Desktop application, and in her latest role is driving the visualization product area based on data and customer engagement.

About Charles Sterling

Charles Sterling 1 1024x676 Microsoft Deep Drive Track at the Data and BI Summit April 22 26

Charles Sterling came to Microsoft from being a marine biologist working for United States National Marine Fisheries doing marine mammal research on the Bering Sea. He started out at Microsoft supporting Excel and moved through a couple of support teams to being an escalation engineer for Microsoft SQL Server. Taking his love for customers (and diving), Chuck moved to Australia as a product manager and developer evangelist for the .NET Framework. In 2008 he moved back to Redmond as the Visual Studio community program manager and just recently moved to the Power BI team to continue his community passion and looking after the Power BI influencers and MVPs.

About Kasper de Jonge

Kasper%20de%20Jonge Microsoft Deep Drive Track at the Data and BI Summit April 22 26

Kasper de Jonge is a senior program manager on the Business Applications team at Microsoft, where he has worked developing features for Power Pivot and other Analysis Services products such as the Tabular model and Multidimensional cubes. He is a frequent speaker at conferences such as TechEd, SQLPASS, and SQLSaturday, and is the creator of http://www.PowerPivotBlog.com, one of the leading Power Pivot websites.

About Kay Unkroth

Kay Unkroth is a Senior Program Manager in the SQL Server Analysis Services team focusing among other things on improving SQL Server Data Tools (SSAS). In this engineering role, he works closely with customers, product management, field representatives, consultants, and support engineers to gather feedback, define target scenarios and functional requirements, develop program plans, and execute on them. Kay has 20 years of experience in the IT industry as a support engineer, consultant, developer, entrepreneur, and technical author.

About Nikhil Gaekwad

Nikhil Gaekwad is a Product Manager on the Power BI service team at Microsoft. His journey at the company started off in 2015 on the Windows Wireless team, where he spent two years envisioning, building, and shipping features in the Wi-Fi and IoT space to millions of customers around the world. He has always had a strong passion for working with data and transforming it into actionable business insights. With an aspiration to be influential in the Business Intelligence community, Nikhil joined Power BI in 2017. Most of you will already recognize him from the recent blogs, videos, and presentations from many conferences.

 

About Will Thompson

Senior Program Manager, Power BI Microsoft. Will is a self-confessed data geek, working his way through sysadmin and DBA roles before joining Microsoft. He spent 5 years helping customers in the UK implement and get value out of Microsoft’s BI solution. When an opportunity arose to move into the engineering world he jumped at it, and now helps translate customer and market requirements into new features as a Program Manager in the Power BI team.

Early Bird Pricing Ends 31st of January 

Join your peers & other experts in Dublin, Ireland 24-26 April 2018  by registering Early Bird pricing, a savings of €400

Register Now

DCI Logo Microsoft Deep Drive Track at the Data and BI Summit April 22 26

 

Let’s block ads! (Why?)

Microsoft Power BI Blog | Microsoft Power BI

Power Query – Consecutive Days

Hello P3 World! My name is Krissy Dyess and I havehad the most amazing experience working as a full-time consultant at Power Pivot Pro since April 17, 2017!

Today is 1/9/2018 so that would be…

266 calendar days, if you DON’T include today’s date

267 calendar days, if you DO include today’s date

182 calendar days, if you include ONLY business days, excluding Saturdays/Sundays & 6 holidays

… and there are many other FLAVORS of responses that could be listed here as well!

Funny, how a simple intro can branch off into many different paths of interpretations – this is pretty much the world of data and analysis that we live in as data professionals!

For any given question, there can be MANY different ANSWERS, depending on what question you are REALLY trying to answer. Additionally, there can be MANY different SOLUTIONS!

In my 20+ years working with data, it seems like a good deal of my time is spent PREPARING the data. What does this mean? It means applying logic to structure the data in a way that is best for your data model and/or analysis.

Today, I would like to share a common data problem and 2 different SOLUTIONS used to PREPARE a set of data for Power BI.

And like most problems, there is usually more than just one or two ways to create a solution. So, if any of our readers out there would like to share any additional ways to solve this problem, please feel free to leave your ideas in the comments below!

Sometimes it’s necessary to create a data set that contains a consecutive list of dates and a value for a data point that will be measured or visualized. In this example, the Source Data shown has 7 records, starting with 07/31/2017 and goes through 08/10/2017. Notice that there is a gap between 08/05/2017 to 08/10/2017.

image thumb 55 Power Query – Consecutive Days

Source Data and End Goal

The end goal is to have consecutive days. Now, the end goal could be consecutive days through the last date available (08/10/2017) or through today’s date or etc. In my example, I will go through today’s date but depending on your problem and your end goal this could be different.

Why solution in SQL? Well… because until Power Query came around, SQL was my best friend when I needed to do any data manipulation!

For those of you looking to learn SQL, this will be a good example to follow along with. For those of you that don’t care to learn SQL, I’ll also show another solution using Power Query.

I’m using SQL Server Management Studio (SSMS), my local workspace, and then creating a New Query workspace.

image thumb 56 Power Query – Consecutive Days

New Query

In order to create a table and populate that table with example data, I select New Query and use the following scripts:

SQL Step 1

image thumb 57 Power Query – Consecutive Days

SQL Step 2

image thumb 58 Power Query – Consecutive Days

image thumb 59 Power Query – Consecutive Days

The SQL Script is entered in the New Query window, Execute or F5 is used to run the SQL script and the results are displayed in the Results Pane.

image thumb 95 Power Query – Consecutive Days

Executed Query

Voila! I’ve created a table with example data by running the above code!

image thumb 60 Power Query – Consecutive Days

Source Data

And we can see the 4 day gap between 08/05/2017 to 08/10/2017.

The next step would be to have a complete consecutive set of calendar days available in a table. Now, you might have a calendar table that you can already use for this step or you might not! There are many ways to create a calendar table! But for this example, I’m going to show a different technique to get a set of consecutive calendar dates for the time frame I’m interested in building.

SQL Step 3

image thumb 61 Power Query – Consecutive Days

 

image thumb 62 Power Query – Consecutive Days

Voila! I’ve created a consecutive calendar table with example data by running the above code!

NOTE:  If you are following along, your dates will be different based on when you run the code – this doesn’t matter for understanding the concept.  The purpose is to just get a set of dates, they can be any dates.

image thumb 63 Power Query – Consecutive Days

Consecutive Calendar Table

SQL Step 4

image thumb 64 Power Query – Consecutive Days

image thumb 65 Power Query – Consecutive Days

We can see that we have 161 rows of data starting 07/31/2017 going out until 01/07/2018.

image thumb 66 Power Query – Consecutive Days

Join Calendar

SQL Step 5 – Final Step

Which brings us to the final step, which is to join the result of the above step with the actual store quantity data to get the actual quantity for days where a quantity was available.And to do this, I created the final SQL query:

image thumb 67 Power Query – Consecutive Days

image thumb 68 Power Query – Consecutive Days

image thumb 70 Power Query – Consecutive Days

Final Join

And now we have the final SQL query & result set –

image thumb 69 Power Query – Consecutive Days

image thumb 71 Power Query – Consecutive Days

Final Results

Now, can we get to the same results using Power Query? Yes, we sure can!

Power Query Step 1

Let’s start with the example data. Since I started with SQL, I can just copy and paste my same example data from SQL into Excel. Then in Excel, I can use Insert > Table and to create a table named ‘StoreQuantity’. So far, so good!

image thumb 72 Power Query – Consecutive Days

Source Data

Power Query Step 2

Then since I’m using Excel 2016, I can use Data > From Table/Range to load and launch the Power Query Editor window with the example data.

image thumb 73 Power Query – Consecutive Days

From Table/Range

Power Query Step 3

 

The exact same example source data is now loaded into the Power Query Editor! Voila!

image thumb 85 Power Query – Consecutive Days

Power Query Editor

Power Query Step 4

I’ve renamed the source data query to ‘Staging_StoreQuantity’. And I’ve created a “Staging” folder for the data preprocessing steps.

image thumb 86 Power Query – Consecutive Days

Renamed Query

Power Query Step 5

For the next step, I can right click on ‘Staging_StoreQuantity’ and Reference or Duplicate another starting source of data.

Now, there are performance reasons that I might choose Reference over Duplicate.

If I were to choose Duplicate, Power Query will locate and retrieve the data again. Depending on the source of the data and the size of the data, this could take some time. However, if I choose Reference, then I’m using the data that is already loaded in the Power Query Editor. So, in this case, I’m choosing the Reference option!

image thumb 87 Power Query – Consecutive Days

Reference Source Data

Power Query Step 6

I’m going to rename the new query to ‘Staging_StoreQuantityAllDays’. And use the Home > Group By button to create a unique list by [store_id] of the first [create_date] where we have a quantity.

image thumb 88 Power Query – Consecutive Days

Group By

Power Query Step 7

Next, I can add a custom column expression using Add Column > Custom Column and the Power Query List.Dates function to create a list of consecutive dates for each [store_id]. I will use the [min_create_date] as the starting date in the expression and go out 365 days.

List.Dates(#date(Date.Year([min_create_date]), Date.Month([min_create_date]), Date.Day([min_create_date])), 365, #duration(1, 0, 0, 0))

image thumb 89 Power Query – Consecutive Days

Custom Column

After adding the [dt] custom column as a List, you can use the Expand to New Rows…

image thumb 90 Power Query – Consecutive Days

Expand to New Rows

… to get one row for each item in the List. Boom!

image thumb 91 Power Query – Consecutive Days

Distinct List

Just a few more steps to change the [dt] format to a date, filter steps to keep only the records through the current date … and Boom!

We now have a table with consecutive dates for the [store_id] starting with 07/31/2017 through to today!

image thumb 92 Power Query – Consecutive Days

Consecutive Date Table

Power Query Step 8

Finally, I can use the Home > Merge Queries as New to create the final query.

image thumb 82 Power Query – Consecutive Days

Merge Query

Staging_StoreQuantity is the resulting column, we use the Expand double arrows to select ONLY the [qty] column from our original data source.  I deselect the use original column as prefix so that only the [qty] name is returned for the column versus Staging_StoreQuantity.qty.

image thumb 83 Power Query – Consecutive Days

Expand Double Arrows

I renamed the final query to ‘StoreQuantity’ and I’ve created and named a new folder called “Data Model”, to store the final query that will be loaded to the data model and that’s it!

image thumb 93 Power Query – Consecutive Days

Final Query

You can see that we can use either SQL or Power Query to take source data and manipulate the data into the SAME desired results!

Which method is BETTER? Well, that depends. If you don’t have SQL or prefer not to use SQL, then Power Query is a GREAT option!

As for me, I have to refer to the song… “Make New Friends (Power Query) But Keep The Old (SQL)… One Is Silver And The Other Gold!”

Happy New Year Everyone wlEmoticon smile 1 Power Query – Consecutive Days

Please see the attached Excel file for the Power Query solution and the attached SQL script file for the SQL solution.

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 http://foofacing 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

Let’s block ads! (Why?)

PowerPivotPro

Power BI Desktop January Feature Summary

Happy new year to everyone! We are kicking off the new year with an update to Power BI Desktop focused on incremental improvements to popular features you are already using, including automatic date hierarchy, data label and axis formatting, and our relative date slicer. The ability to hide pages is another big update that gives you much more flexibility over how users consume your reports.

5d7f8edd 60a2 45e6 8cd8 5a7fa35e97b5 Power BI Desktop January Feature Summary

Don’t forget you can now get Power BI Desktop from the Windows Store if you’re on Windows 10!

003c6541 690b 4fef aad3 28981197f13c Power BI Desktop January Feature Summary

Here’s the complete list of January updates:

Reporting

Analytics

Custom Visuals

Data connectivity

Other

For a summary of the major updates, you can watch the following video:

While creating your reports, you may have needed to hide a page or two perhaps because of in-progress work. You still wanted users to have access to other parts of the report. Hiding pages is also useful when you start using drillthrough or creating your own navigation through bookmarking, and you don’t want you users to access a page through the normal page navigation.

Starting this month, you can now hide any number of pages you want through right clicking the page name and selecting hide.

ce8aa5f5 b857 497f 9867 830e3b5c1728 Power BI Desktop January Feature Summary

While you are editing the report, you’ll still see the pages, but they will look different to indicate they are hidden. You won’t be able to see the pages at all when you move to reading mode.

9f62ffba 6982 496a bfd6 a918edf820f2 Power BI Desktop January Feature Summary

Watch the following video to learn more about hiding pages:

You can now control the background color for data labels in Cartesian and map visuals. This is useful for stylistic changes and readability, especially when you have a situation where the data label is halfway over a data point. You’ll find the option under the Data labels card in the formatting pane. f2ee1553 3e81 4bab 9a57 b8c7f70c7926 Power BI Desktop January Feature Summary

Watch the following video to learn more about data label background colors:

If you have a chart where the axis labels are being cut off, previously your only option would be to increase the size of your chart.

919f3d4c f87e 4739 87df 1bfaa0bf2495 Power BI Desktop January Feature Summary

Of course, that isn’t a great option for many of you as report page space is limited, so this month we are adding a maximum size control for the axis to the formatting pane. This new slider can be used to increase the percentage of the chart used by the axis labels.

356e90dc 2d36 4a44 b583 702bf6b7c2ff Power BI Desktop January Feature Summary

Watch the following video to learn more about axis size control:

Another new axis formatting option we’ve added is the ability to control the inner padding between bars. You can make the inner padding up to 50% of the bar’s width.

01421a97 d97c 4f08 93be 2ca2d43c9e29 Power BI Desktop January Feature Summary

Or all the way down to zero to get a histogram effect.

5276d74e 5c51 4c7c 9ca8 3061f5e81f0b Power BI Desktop January Feature Summary

Watch the following video to learn more about padding control:

For a long time now, we’ve been automatically creating a date hierarchy for you whenever you are using our internal date table. We haven’t been showing that hierarchy in the field list though. Instead we have just created it automatically for you when you drag your data column into a visual. With this latest update you can now see the entire hierarchy in the field list. You’ll need to turn on the preview feature switch under File > Options and Settings > Options > Preview features.

1f187d1a e617 463a 8bc4 945bad4625f6 Power BI Desktop January Feature Summary

This is useful not only because we are being more transparent about automatically generating this hierarchy, but because you can now use individual fields from the hierarchy in your visuals just by dragging or checking the specific level you are interested in. Previously, to do this you would have needed to put the entire hierarchy into the visual and then remove the levels you didn’t want.

Watch the following video to learn more about date hierarchies:

When using the relative date slicer, you may want to start further back in time than today. This is very common whenever your data is complete until a few days later when all sales are finalized. You can now do this with the relative date slicer by setting an anchor date. Once you have an anchor date set, the slicer will be evaluated relative to that specific date. You can set it in the date range options in the formatting pane.

fd76b914 84e9 4fdb b1b1 72618bba2222 Power BI Desktop January Feature Summary

Watch the following video to learn more about anchor dates:

After bringing Q&A to Power BI Desktop in December, we’re continuing to improve the natural language engine itself. You can now ask top-n type questions such as “What are my top 3 products by sales.”

Or “top manufacturer by NSAT”

Both top and bottom work, so try it out on your data!

This quick measure was suggested by Daniil Maslyuk in the quick measures gallery. It calculates the Pearson correlation coefficient between two measures within a certain category – a really common statistical measure of how related two values are.

With the new PowerApps custom visual, you aren’t only able to quickly find insights in your data, you can take action on it as well. PowerApps makes it easy to build automated workflows, and with the new custom visual, that experience is embedded right in your report. If you want to learn more about this visual make sure to read our dedicated blog or checking out the custom visual on AppSource.

You can also watch the following video to learn more about the PowerApps custom visual:

The TreeViz custom visual by AutoCont CZ a.s. lets you display your hierarchical data in a tree like structure. You can expand and collapse each level and decide how many children data points to show before grouping the smaller ones into a single child group.

0d5e9484 fda3 4a74 b956 31590bc039a4 Power BI Desktop January Feature Summary

You can check out this custom visual on AppSource.

Watch the following video to learn more about the TreeViz custom visual:

The Funnel with Source custom visual is perfect to track any metric of interest over various stages along with the source of entry of the data point to the funnel. For example, in the case of sales journey data, this visual displays the channel through which any lead enters the funnel and then the corresponding journey through the various stages of the sales cycle. You can also filter data based on the selection of channel and on the selection of any stage of the sales journey.

3dfe82eb 7999 4298 9d61 cbca841c0e5b Power BI Desktop January Feature Summary

You can find this visual on AppSource.

Watch the following video to learn more about the Funnel with Source custom visual:

Box and whisker charts are most commonly used in statistical analysis to see the spread of your data. They consist of two parts—the main body called the box and the thin vertical lines coming out of the box called whiskers. The first quartile forms the bottom and the third quartile forms the top of the box. The whiskers connect the minimum and the maximum values to the box. In addition to showing median, first and third quartile, and maximum and minimum values, the Box and Whisker chart by MAQ Software is also used to depict mean, standard deviation and quartile deviation.

f2a05aca d24e 4e98 a8f2 1862233c03fe Power BI Desktop January Feature Summary

Make sure to check out this visual on AppSource.

Watch the following video to learn more about the Box ans Whisker chart custom visual:

The Agility Planning Matrix Light by Managility is a matrix visual for planning and budgeting. Some benefits of this matrix visual is the ability to selectively drilldown on specific rows and to display currently applied filters on the visual itself.

f008f92f f8c7 4e0f 946e 254e6bcfdcd9 Power BI Desktop January Feature Summary

Check out this custom visual on AppSource.

Watch the following video to learn more about the Agility Planning Matrix Light custom visual:

The Image Grid custom visual by Fredrik Hedenström is like a word cloud for images. You can display many images and choose for the size to be based on the sort order or the value of a measure. You can also decide to show them in a grid view instead with the top visual to be based on the sort order.

83d0207f cb5d 40ee 85a4 eb0484e83399 Power BI Desktop January Feature Summary

Get this custom visual on AppSource, and watch the following video to learn more about the Image Grid custom visual:

The Gantt Chart by MAQ Software visual is used for scheduling and management of tasks. This visual shows additional details of the task by providing a grid where one can view more data related to the task and can display the hierarchy of data category. Also, it can be sorted based on any data point of the task.

1444afdc fb4e 4c7a bf76 d293ea78f4ce Power BI Desktop January Feature Summary

You can download this custom visual on AppSource.

Watch the following video to learn more about the Image Grid custom visual:

One of the most frequent requests from customers using the Azure SQL Database and Data Warehouse connectors has been being able to leverage Azure Active Directory authentication in order to connect to these Azure services within Power BI.

With this month’s update for Power BI Desktop, we’re introducing a new “Microsoft Account” option within the Credentials dialog for the Azure SQL Database and Data Warehouse connectors, allowing users to authenticate using their AAD accounts.

Note that support for this new authentication type in the Power BI service will come in a few weeks. Please stay tuned to this blog for updates on this capability.

7368d923 c7cf 4084 a111 c87d0090b19c Power BI Desktop January Feature Summary

If you’re using the Windows store app, you can now specify the display language instead of using the default experience of the Windows display language. You can also specify the model language separate from the display language. This is useful if you are working for a global company and there are people from all over the world consuming and creating reports in languages other than the default language the report was created in.

You’ll see these new options under the Regional Settings tab in the Options dialog. Changing the application language will ignore the Windows default display language and switch to the chosen language. Changing the model language will decouple the model language from the application language for any newly created reports. (Note that this won’t affect the model language for existing reports.) Changing either of these settings requires a restart.

9e933c84 d619 4d39 a840 d8b5c7010d15 Power BI Desktop January Feature Summary

That’s all for this month! We hope that you enjoy these updates and continue sending us your feedback. Please don’t forget to vote for other features that you’d like to see in the Power BI Desktop.

b6fbb8f7 ba81 4316 9f6e bec3d29025ee Power BI Desktop January Feature Summary

Let’s block ads! (Why?)

Microsoft Power BI Blog | Microsoft Power BI

1/11/2018 Webinar: Integrating R Script and R Script Visuals with Power BI by Ginger Grant

[unable to retrieve full-text content]

Power BI is a great tool for providing in-depth analysis of your data. In this webinar we are going to add R to Power BI to provide not only some R visualizations which are not available in Power BI, but also to analyze the data using statistical methods. We will take a look at what answers Power BI can glean from the data and those which remain unknowable. This webinar will show you how to take your Power BI reports to the next level using analysis you can use in your next project.

Microsoft Power BI Blog | Microsoft Power BI