Power BI DAX Kickstart

×

Bill Pearson
Business Intelligence Architect
Senturus, Inc.

Bill has over 20 years of experience in business intelligence and data analytics with a background in financing and accounting. (He maintains active CPA and other certifications). Bill is an SSAS Maestro (a Microsoft designation), was an eight-year Data Platform MVP, was a technical lead on the global roll out of Power BI and is focused on helping clients implement and use Analysis Services (Tabular and Multidimensional) and Power BI within their enterprises.

Read more

×

0:07
Greetings everyone and welcome to this latest installment of the Senturus knowledge series wherein we tap our industry leading experts consultants and trainers to present areas of interest on topics that are relevant in the industry particular to analysis analytics and bi today. We’re excited to present the topic of power bi DAX and give you a kick.

0:37
On the overview in the mechanics of data analysis Expressions. First a couple of housekeeping items the GoToWebinar control panel.

0:47
You want to move to the next slide bill can be minimized or restored using the orange arrow and while your microphones are muted we do strongly encourage you to enter any questions you might have through the questions panel in the GoToWebinar control panel. We generally are able to answer questions during the webinar.

1:10
But if we are unable to for any reason we will cover it in a completed questions log doc that we will post along with the slide deck and a recording on our website which brings us to the next slide. We always get the question of can I get the presentation deck in the answer is absolutely it will be available on senturus.com at the link. You see above you can select the resources Tab and then resources Library alternatively you can click the URL that should be posted in the GoToWebinar control panel chat window and be sure to bookmark that resource Library as it has tons of great valuable free content addressing a wide variety of business.

1:51
Analytics topics our agenda today after some brief introductions will get into an introduction to data analysis Expressions will talk about various vehicles of delivery.

2:04
In other words, the calculation types and calculation columns and measures will talk about the different function types the that are bucketed into several different groups and give you examples within each of those groups will summarize will talk about some great additional resources that’s interest has to offer Then we always wrap up with the question and answer session with any remaining time our presenters today. I’m pleased to be joined by Senturus Bill Pearson. It was a Microsoft business intelligence architect with Senturus. He’s a 30-year bi veteran and a CPA with experience in accounting and finance ERP and CRM systems. He’s a Droid and Legacy and modern platforms and is proficient across the Microsoft BI stack and brings a true wealth of hands-on experience.

2:51
With his work in power bi SQL Server SQL Server analysis Services SQL Server reporting services and Excel PowerPivot. My name is Mike wine our I’m a practice area director here at Senturus and solutions architect. And the host of our Senturus has knowledge Series. So before we get into the content with our esteemed presenter, I was like to get a finger on the pulse of our audience here. And so we’re going to take a quick poll here.

3:17
Then I’m going to launch in the question is just getting a Handle on the overall experience level of our audience with DAX expression. So are you brand new to this? And this is really what that audience is kind of this is the audience this presentation is kind of geared towards have you played around with it? Are you are you a beginner where you write basic decks queries. Are you intermediate where you actually create calculations? Maybe perhaps somewhat complex calculations.

3:46
And then are you an advanced level where you can meet 1 business requirements with DAX above and beyond great. So we’ll give people a few more seconds to respond to this got about 3/4 of the audience get those votes in.
4:04
All right. We got a good 80% there and we’ll share the results. So we have a pretty good distribution here plenty of you are new to this so 1/2 or kind of in the in the beginner 3/4 in the true novice other quarter in beginner and then got a few gurus out there.

4:22
So thanks for joining us and I’ll give you a sense for kind of your audience Bill and their level of expertise and with that I am going to hand the microphone in the floor over to you bill for the Body of your presentation. It’s all yours.

4:37
Great. Good morning. Good afternoon, wherever you are on the planet. My name is Bill Pearson. And this is introducing data analysis expressions or DAX as it’s finally referred to DAX is most of us know serves as a language a query language and a manipulative manipulation language for power bi Excel PowerPivot and SQL Server analysis Services tabular the I don’t know how many of you have worked with any of those but it got its start in PowerPivot. I was really involved in the rollout of it.

5:07
For Microsoft and it’s going through some rapid changes. Everybody’s probably noticed as 45 to 65 or more updates a month. So it’s amazing to stay on top of it. You have to have to put some time into it and DAX itself Grows by Leaps and Bounds as well DAX initially seem simple, but the learning curve can increase dramatically and at the worst possible time, so you want to try to look at examples and I’ll give a little advice here of ways to become familiar.

5:37
At least comfortable with DAX as we go along DAX comprises programming Concepts that we will be unfamiliar to many that have worked with SQL in other languages even MDX those are different languages and there are lot of things that look kind of funky when you first look at it and all that but it will grow on your pretty rapidly and will discuss some tips and he’s in that effort as I’ve already noted.

6:02
DAX is an expression language function calls Drive execution elements DAX are derived from Excel. A lot of it is Excel functions. A lot of this will be familiar things like Psalm and count and things of that nature SQL and MDX an example of a simple formula and this is probably real simple to everybody. We just taking a customer count. So I’m just basically saying count customer and customer key.

6:28
The only difference in this in Excel expression is that we’re basically telling it the table in power bi in this case and then the take the column in the table and turn it to count those.

6:40
The general format again is table name and then column name you can put the table name in parentheses. You don’t have to if the table name doesn’t contain spaces the best practice. Therefore is to not use spaces before you get too far into DAX. If you’re just learning it go over the best practices. You can Google it and find a lot of practice best practices of things that they suggest that you do and don’t do and you’ll start out with a good habit instead of having to go back and think change.

7:10
Things you’ve done in the past and that sort of thing. It’s always best to start out. Right the table name can be omitted the current Table search for the column name again. The best practice always use the complete table name and column name to avoid ambiguity. There’s a column name in the model that doesn’t you know, it doesn’t appear anywhere except the one place you can get away with the column name, but always think about what might happen if the model changes later if it grows and also things like, you know, you want to make it easily.

7:40
Lee read by whoever follows you want it to be self-documenting. So it’s always good to use that name and avoid ambiguity brackets for the column name cannot be omitted the primary vehicles for DAX and that’s just my terminology.

7:56
The primary vehicles are basically calculation types and they can best be employed through calculated columns and measures are two different ways to create the different functions that you Bill from DAX formula example of a calculated column and I make the names kind of long and you got to find it. We’re going to mention this more than once naming a calculated column or measure is pretty significant because somebody’s got to look at that name and know what if you can imagines even if we’re a mile long that would be hard to do in the number of characters that you have. Well, what I’m doing here is key in it to my DAX model so I can easily find things you may have other ways to do it. You probably would you’d probably want to say just product.

8:41
Or something like that here. We’re basically taking the list price column minus the standard cost and it’s just as straightforward as that again. You’re probably thinking you wouldn’t need the parentheses here. That’s correct. I’m not parentheses the Yum ‘ okay. So let’s take a look at a demo of creating calculated column. The far right column is the product. Margin. There’s a way you can change the name in there. Even though your formula uses something different.

9:09
I’ll try to stay out of power bi too much because we got a lot to cover but I’ll actually show some of that as we go along here. So we just pull that up right quick.

9:21
Okay, the calculated column here. We see product. Margin. We’re basically all you really got to do is click the visual. It’s in I’m not going to get into the visuals themselves too much. It’s kind of beside the point we could be doing this with other tools as well. But I can looking product here and it basically isolates what we have in the visual itself. And here we see the two types of vehicles.

9:45
I talked about the calculated column in the measure notice the difference in the icons and we’ll talk about what’s better for certain cases and all that shortly? But let’s just look at this first calculated column.

9:58
Okay. See I’ve got it over here is product. Margin you can change when you drag the values in you can change the names and that’s what actually shows up on your visual. You see up at the top here. We got sorry about that. We got the intro Cal call product margin and we’ve got product list price – product standard cost and that’s what’s given us that a lot of times when I’m doing a calculation. I like to put the pieces that make up the calculation total into the visual just to make sure everything’s working right I can do it.

10:28
Visual check and make sure that my DAX is correct particularly important in your early use of – always going to make self-checking features and I’ll show some more ways to verify your work as we go along. These are just basic ideas to help you learned acts in a way nothing. Nothing super important. Not really a best practice or anything like that. Okay measures creation. The other option for a vehicle is a measure.

10:58
A measure in the product table that computes the total quantity based on order quantity and the related internet sales table. So we’re doing two things here. We’re basically well, let’s just talk about the basic formula first. This is just a summary we’re going to do the total quantity. The sum is internet sales time for the order quantity. So we’re summing up the order quantity in the actual table. Okay. So let’s take a look at that.

11:29
Okay. I’ve got a slicer over here and this is just a help me with.

11:36
Filtering down my selection and stuff like that, but you can really see all I’m doing here some in this they come out to be the same number is not really of the whole lot of impact, but that’s basically what I’m doing here. So I’m really just saying quantity and then sum it up and it sums up to be the same number. So there’s not really a whole lot involved in this one. We look up here the formula bar at the top square. I put my DAX there’s like my function in Formula right there. So I’m showing sums there.

12:06
Okay, creating the measure again. This is just a picture of it the measures to the right there. The standard column summarizations turned on. That’s why we’re seeing the same total there if we had summarization turned off we would see the details for all the different rows and stuff. You can do that.

12:24
You can actually write reports that do a lot of detail reporting in power bi but it kind of gets around the purpose of summarization in aggravate aggregation that typically winds up in dashboards Okay. Sorry Bill. You’re just hearing about to steer about to Define that the difference in a calculated column versus a measure carry on right?

12:42
Okay, calculated columns are best when we need to slice or filter on data measures our best when we need to calculate percentages calculate ratios perform complex aggregations and fully take advantage of filter context as well as hierarchies and things of that nature because before I go into the bottom part here measures are basically taken advantage of what’s called context. They’re basically saying wherever I imagine a pivot table and when you move things around in a pivot table they change like if you change it from a detail line to a total line, it basically goes to total is smart enough to know where it is. It’s context evaluation when you use a calculated column that’s called row evaluation that calculation happens on every single row. So if you’ve got say a table that’s a million rows.

13:35
And you’re competing that power bi is extremely efficient with the engine the x velocity engine. It’s also really good with compression. But when you’re talking about anything in a calculated column you’re talking about every time that models open that calculations open. So everything’s running in there. So if you do way too much of it because it’s easier or whatever reason and I see it a lot but I go out on implementations.

14:03
That’s um, that’s a No-No you want to we try to keep that in the measures because measures are only there when we call them and use them and their cognizant of where they are and they basically shift to whatever they need to be in the context. That’s a hard concept when you first see it, but you’ll get used to it after you work for it with it for a while a great way to think of DAX functions and there are over 200 now are as many I think there were four introduced in the last months of release are as members of working.

14:35
Now this is my take on primary groups and everybody’s got their own ideas. Probably there’s not any official groups, but some of these just makes sense like text you things like left concave, right, you know mid all the other things that you know from Excel or in there. We’re going to take a look at concatenate and just a minute aggregate has got average count Max some something called some X whole different discussion, but there’s a lot of different things out there in addition to regular Excel.

15:05
INS the math and trig functions, we got some interesting ideas and here on ceiling nth for integer round and lots of other method trig functions. Some of them are very arcane. I won’t get into all of them. They’re like real special uses that we don’t really have anything for today date and time kind of speaks for itself.

15:25
That’s basically the physical date stuff like extracting a date to put date and then put like 2010 1/5 it I’ll give you the date back in that will look at some examples date value year etcetera time intelligence. One of the things that this does exclusively as opposed to excel functions and do a lot of stuff. If you’ve got a date table out there that you can base it upon you can essentially point to that date table and say as of this date in this month, what is the month to date it figures all that stuff out for you?

15:59
And what’s really cool is if you make a function and we’ll look at some of these if you make a function for Month to date and then you put that inside another function that says same period last year. It just automatically pulls it out for you don’t have to doing this calculations in Excel or even down in your data warehouse or anything like that is smart enough to know that the time intelligence functions are themselves just a really beautiful thing Foundation functions. This I use I use this term myself.

16:28
I’ve just always used it, but they’re really a whole lot of other type functions like calculate where you can reset the Context we talked about and things of that nature. So let’s take a look at some of these now. I’ve got a marked out inside by PB IX my power bi model. Let’s take a look at concatenate works almost exactly like you would expect it to if we look at say I don’t know over here and say take a look at the calculation itself. I’ve guided inside customer what I’m doing. I had a business need a customer asked me to concatenate.

17:05
Innate the first and last names. It’s real simple scenario, but I want to put it out like you see in the picture here atoms are and I want a single spaced, you know name of a customer. I could also add in all kinds of other things about the customers I could do all sorts of other things like put in things like they’re, you know, put commas between the words and you know sex date of birth. All those kinds of things could be added in there, but we’re basically doing a very simple calculation.

17:35
Here the problem with the concatenate function. If you’d call it a problem is basically you have to Nest it. If you have more than two things you’re concatenating.

17:44
So you would you can easily do that with the Ampersand operator most a lot of the DAX functions have operators and you can just use the operator and just string it along and say concatenate all these things just by putting an operator an ampersand between say the column the column names and that gives you the capability to do a space with a AMA between two spaces are all kinds of nuances. It’s just way easier to write pretty easy to follow. So it fits all the rules for you know, best practice type thing. Let’s take a look at count.

18:19
Coughs one of the basic mathematical functions aggregation functions actually number customer phone lines are just want a quick count here to show how many somebody asked for how many phone line distinct phone lines we have in our customer database. This is what’s called a card in the visuals. You can always see what visual you using in by power bi when your highlighted there you basically see it highlighted over here in the visualizations. Okay. So let’s look at count.

18:47
I’ve got customer phone here. So they’re okay DMC. I’ve got a ceiling and interior two side-by-side to show how they work notice that the sum of the standard cost is coming in at four decimal places. The ceiling function basically says, what the top of that is and the nth is basically showing what you knows, what it round is to if we’re just rounding it to an integer.

19:17
Got both of these in here. This is a very straightforward as the rest of the stuff is here’s ceiling here got ceiling product cost and then you tell it how many decimal places to use here since that. I hope that makes sense. I’m telling it to go over two decimal places. So it tells me the ceiling is this there’s also a floor. I just didn’t want to beat the comparison to the ground for nth here. We got it’s really nice how you can see all this stuff. It’s a little hard to get used to.

19:47
The functions being up in that upper bar sometimes but you can always see exactly what’s in a function. If something doesn’t seem to be working correctly. We just basically say anent and then the column name and it gives me the integer for that formatting as far as dollar and all that kind of thing. You do all that with the underlying value so we could see that pretty easily if we need to go in and change that to be something else.

20:13
Date and time Basics. We got date value and time and time value. They’re pretty self-explanatory. But let’s just take a real quick look in a simple table. I’ve got here. I’ve got date and time date and this is just either fed it in. I took it off of URL. I could get in a lot of different places even out of a table. Obviously. I’m saying 20 2011 19 and it’s basically giving me the date and that I’ll also is I influenced by your Locale settings on your local.

20:42
LPC OK date value pretty much kind of the same thing.

20:48
It’s just giving us a date value and notice. I’ve got these inverted and it still picks it up in the date value function. I’ve got them in a different order. It’s still picks up a smart enough to know what that is time is really the same thing as well. But I’ve got it formatted. I’m telling it time 1330. It’s on Military Time some call it. There are other terms for it. 13:30 is basically 130 in my local.

21:13
Settings Regional settings, whatever you want to call those and then we’ve got time value here.

21:20
Notice, I’ve got it here almost in clock format. It gives it back to me in my local settings format. So you can do a lot of things as far as International translations on these things all sorts of things. Just remember it’s not just the function telling it what order to put it in. It’s also your Regional settings have something to do with that. Same things true with currency, etc., etc.

21:45
Total quarter to date and total year today. These are the time intelligence functions notice. There’s a difference now between date and time Basics and time intelligent functions. There’s time functions. There’s time intelligence functions time intelligence again, is that thing? You can only get here it’s not something you can get in Excel. You can probably do functions in the underlying database to replicate what these do but you’re not going to find anything more useful than these time.

22:14
Intelligence functions. Let’s take a look at total quarter today.

22:21
Notice you can see us in the internet sales table. I can put the by the way this is kind of important to I calculated columns have to be in the table that the calculation occurs in calculated measures can be anywhere. So one of the best practices is at least for user friendliness is to create a table and put all your measures in one place where people can go find them a user’s not going to necessarily have the cognizance that yet. They won’t necessarily know.

22:50
I’ve got to go to the Table, it just makes it a lot harder this whole Easter egg hunt of clicking on the visualization and having to go to it and stuff like that. So you can make life a lot easier for your users. Just trying to think like one and the good thing is most of us go through that phase learning this stuff. Anyway total quarter to date you can see up at the top inside. The total quarter to date function is another function.

23:15
It says some internet sales amount and then we’ve got the context will give it and Burr, you could call time intelligence and calculate and several other things basically the capability to filter something that you’re already calculating. So you could create a calculated column called some sales amount and put that calculation inside this and this is when it gets really powerful because you can Leapfrog calculations off each other and make it really Compact and again the biggest challenge with this stuff once you understand how it works.

23:50
X is easy to work with the biggest challenge is named in these things where it makes sense to somebody that’s like a casual user and that’s why it’s really important to probably keep a table or a spreadsheet or even use the properties or pop-ups your you know, when you Mouse over something that pops up you can set all that stuff up to maybe give a little bit more of an explanation or indeed on maybe put a URL in there and take them to a data dictionary of Swords.

24:19
So that’s what that’s doing they’re saying quarter to date it looks at the table. It sees the month and year number and it goes and says what the quarter to date value for that notice was. I courted a date and year to date in comparison here quarter to date changes every quarter notice how it resets at the quarter.

24:41
Okay starts building up like December. Okay, November. These aren’t in order. Sorry about that this little bit confusing I guess. Okay. We’ve got January maybe this time. Well, I probably didn’t order them properly. But what’ll happen is it’ll start it’ll reset at the quarter point and just keep going forward that way so got January/February here notice how it increases for the amount. There’s a quarter date it goes up for the amount difference there.

25:09
Total year today will keep on going for the whole year long and come to the totals. They get bigger and bigger as you go down at the bottom. Just take a real quick. You could total year-to-date same concept is quarter to date again, you just put whatever the function is and give it the date etcetera. So it knows to go look at that date Row in the date table and knows where it is in time. You just have to have a date table to do this.

25:35
If you don’t have a date table that has a distinct Row for every single day in your Calendar it won’t work. It’s just a best practice. You got to kind of observe. Well, if you start that by month year number, would that give you the right order? So it may I didn’t really look at this. Yeah, there goes like that it goes up. Sorry about that. Sorry about that. Sorry. I see how it works like quarter to date. Here’s building up to March. It comes to a total then it resets here notice. What’s happening in the year today column is just gradually increase in every single month. Okay notice it.

26:09
It resets at your end. However, see it’s showing 857 in both places. So you’ll find you’ll find time intelligent something you really want to spend some time on and we got a course that basically covers a lot of the stuff that they’re going to talk about towards the end here that you’ll find very useful in that regard.

26:29
Okay.

26:37
We just looked at that stuff in the wall through a couple of points here. Where does that fit in you might be saying in my building my power bi model and that’s the context. I’ll put cast most of this in today. You’ve got get data transform and then when you’re modeling and visualizing that’s when the DAX comes to be most heavily important when you’re doing transformations in query editor what used to be called Power query recently used to be called Power query, but when you’re doing it in there, it’s done with that.

27:07
I’m language you don’t use DAX in there but inside the model and individualization’s that’s where you come to the decision. You need a calculation and the cool thing about power bi is you can do it in just about any portion of the app. We got two places here that it’s really good for you can do your calculations in here inside the data View and you can also do it while you’re writing a report.

27:34
So if you’ve got the capability to you know, no actually edit and interact with the reports you can do all of that stuff in both places where it gives you the capability to be able to go in and actually, you know, see that it’s working and things of that nature. Okay. Let’s talk about a few points here as you learn DAX.

27:52
It’s really important that we don’t try to memorize I know a lot of people I’ve been in interviews before and sometimes it’s hilarious, but they want you to have memorized the DAX and probably don’t even know what themselves but trying to memorize And complex DAX like solutions to things is not as important as gaining an understanding of the concepts. You can always look up the functions. There are literally thousands of sites. Now where people are explaining DAX and all that. Be careful about some of that advice by the way, always good luck with the Microsoft Source if there is one because that’s been validated most of the stuff is correct, but you can always look up functions its syntax and samples Etc. memory.

28:37
Can be wrong or incomplete as we all know particularly anybody that’s getting a little bit older knows that and you can you know, pretty much have errors that way if you’re not real careful as we done in the sample.

28:49
We include in the course keep a PB IX or power bi model full of working samples carry around with you on a you know, A draft thumb drive or whatever and it could save all kinds of time and misunderstand if you got to come up with a way. I remember I did that before. I remember I’ve you know seen that done you’ve have to work an example that way this coolest thing about the compactness of the power bi file. I call it a lab file, but I like to have that with me wherever I go preferably on a shared drive or something. Okay, check out release notes that accompany power bi updates.

29:27
I mentioned earlier the frequency and Volume of updates. It’s almost impossible to keep up with but stay on top of it particularly new DAX function comes out because something you are doing with say three or four different functions stacked on each other. Now, you can probably do with one function time intelligence in is an example of that.

29:48
There’s a core function and formula that basically you can do anything like quarter and three quarters actually one that they don’t have in there, but you can do it using some of the others the standard date quarter and doing compilations and things like that. You’re also going to run into weird stuff about calendars that aren’t necessarily calendar calendars if you know what I mean, there’s different types of calendars and that way you can you know, you basically can go in and create a function that works across aggregate data weeks, you know days any kinds of things you want to do you can do that.

30:26
Constantly deepen your understanding of DAX Behavior, which can come sometimes be surprising people internal to Microsoft have a list where they talk about the latest functions and they say it did something I didn’t expect but it works. It just doesn’t work in the way. I expected always stay on top of those kinds of things. So you’re not surprised at a bad time put thought into naming your measures. I think I put a lot of time in discussing that make them easy to locate and easy to identify what their purpose.

30:56
Laughs is maintained environments to support effective Learning and Development.

31:01
Not just for yourself particularly with that laboratory file I’m talking about but you need a development environment obviously and a testing environment and obviously they’re going to production with that but you want to always be able to not just put a, you know visualization out there that gives you a number but show how it actually works so it can be verified independently in a different context and maybe You know ascertain that works for you embarrass yourself in front of an employer or client. Finally collect working samples articles and references any of us that work with languages always were always curious.

31:39
We always want to stay on top of the different things that we can do, you know, and you get really good ideas when you go out and see what the community is doing out there power bi has one of the greatest communities I’ve ever seen as far as even if you want a function that doesn’t exist or what Some kind of capability and power bi you can basically go out there and put a suggestion in and people can vote it up and I’ve seen things get bumped up to where they’ll come in and maybe two months later. They’re paying a lot of attention to that and it’s a great Community as far as sharing results and things of that nature, too.

32:15
Summary, we’ve introduced data analysis Expressions. I hope vehicles of delivery the calculation types remember are calculated columns and measures the functions themselves. We just talked about some examples. Remember the kind of in a group and you a lot of times you can call it whatever you like. I’ve seen a lot of different things use their but that’s pretty much totally up to you. I hope this has been helpful.

32:43
Yeah, that’s great stuff bill. So what I see there is and stick around everyone get your questions into the question Pane and we will answer those in just a few minutes here, but I think you can see that DAX is easy to get started with especially if you’re like most of us you have a background in Excel, but then it can very quickly get very complicated. The other thing that we see is that the introduction of powerful tools like power bi is that the DAX?

33:11
Image really introduces a level of power but also complexity that you don’t typically see in your you know, your typical Cognos environments or even I’d I think you could make the argument your average Tableau environment with the level of power that you have with DAX and so what bill has touched upon here is really truly just the surface of it designed to maybe give you a level of comfort and maybe go out and start trying some of this stuff on your own.

33:42
But along those lines we do have we have our annual cyber sale going on here where we’re offering 50% off all of our instructor LED training and ends tomorrow, but that does also include our newly added DAX fundamentals class taught by Mr. Pearson himself and we’re offering that right now for 299. So you’ll come out of that with a great handle on – you’ll be you’ll be you’ll be dangerous and you’ll come out of there with that at least a good starter of that PBR.

34:11
AIX file with those records calculations that come out of that and that’s in my mind worth it for that practically on its own.

34:20
So definitely go ahead out to that link and on the next slide if what we what we find is that folks go through our training or learn through various methods, but then they run into specific issues around their particular business scenario in the dashboards that they’re trying to either create or to play those two the Fries, so we offer power bi mentoring to extend that formal training into your actual environment using your data your application using experts like Bill in your environment. You have all these years of experience with those hundreds of DAX equations and things of that nature and we can do that on site or online to either small groups or two individuals.

35:04
And then if you want to go to the next slide here talk a real just a couple minutes about Senturus and Who We Are and we are a full-service analytics bi consulting company. And if you’ll go to the next slide, you’ll find out that our clients know us for providing Clarity from the chaos of complex business requirements, Myriad disparate data sources and constantly moving targets. And our name is has the strength that has because of our ability to bridge the gap between it in the business users. We deliver solutions that give your organization access to Reliable.

35:41
Analysis ready data across your organization. So you can quickly and easily get answers at the point of impact in the form of the decisions you make and the actions you take in the next slide. We talked about how our Consultants are leading experts in the field of analytics with years of pragmatic real-world experience guys, like Bill who’ve been around the block a few times our favorite Urban Cowboy and we are we are in migration experts we can help you get from point A to point B, whether that’s to the next version.

36:11
To from one platform to another or if you’re developing a coexistence strategy across any of these type of the area’s you see in the in the bullet flights including hybrid VI environments. We’ve been at this for a while. If you go to the next slide Bill we’ve been doing this for nearly two decades cross over 1,300 different clients ranging from The Fortune 500 down to the mid-market. You’ll recognize a lot of the names in this slide and we’ve completed over 2,500 analytics projects for area.

36:41
Including the office of Finance sales Human Resources it across the board. So we hope that you’ll consider us for your next analytics project or for any training you might need in Cognos power bi or Tableau. We have some great upcoming events.

36:57
If you want to move to the next slide since I guess, it’s December, we’ve only got one to show you right here, but bookmarks interest.com slash events, and we’ve got a great one coming up on December 12th where we talk about steps for building better looking dashboards be beyond the default dashboards that people tend to end up with and that’s taught by another one of our amazing Senturus consultants and trainers Monica Van Loon and that’s real useful. Even if you’re working with power bi or another app to because best practices and dashboards are often, you know, kind of subjective stuff like yeah, that’s a great fun.

37:34
That’s a terrific point that actually has bearing on the next slide to that these the dashboard Practices are germane to any type of visualization regardless of the tool. So certainly Monica’s focuses on Tableau in that particular one, but the practices are absolutely germane and relevant for a power bi environment. We recommend that you visit senturus.com interest resources here for some fantastic resources. We’ve got a lot of power bi and Microsoft resources out there. We include links in the deck here to some particular ones that are very interesting particularly power bi Priyanka.

38:11
Premium if you’re looking towards again deploying this more at an Enterprise level and you’re interested in what the difference is between basic professional and premium and what to consider as well as leveraging pageant and reports in the power bi service and getting into again some of that Enterprise type stuff where you may be considering the on premise data Gateway. We’ve got a nice blog on that.

38:33
So we’ve got a section with our blog which has nice bite-sized information around what’s top-of-mind its interests and then the resource library has all of our webinar our past webinars and including the one that we’ve presented here today, and then finally we invite you to join us. We have a ton of great training for again power bi Tableau and Cognos bookmark that site whether its corporate training where you need to have a gamut of capabilities and modalities, whether that’s self-paced learning instructor letter in person and mentoring we can combine all that into a bundle that meets your corporate needs.

39:11
AIDS or as individuals we provide all those modalities self-paced instructor-led and mentoring for individuals. So with that will come to the last slide here or the second last slide and we’ll get into the Q&A. So, please enter those questions there and the first question we have is really is there a way we’re talking about some of the time functionality here Bill to specify the year start and end month.

39:41
For a fiscal year in those time intelligence functions based on the year. Yeah, and remember that’s that wouldn’t be time intelligence what you’re talking about. There is extracting information from a date field. So it would be that first set but there is there’s an e Om function and just go through those functions and I’ll tell you something else about learning functions. It’s almost like it would be beneficial if everybody had time to read a dictionary every now and then if you read through those functions, you’ll be aware.

40:11
Or things that you didn’t even know you had in there and just a quick read of what they all do and staying on top of it that way particularly when you’re first getting started but there are functions that handle that the things that can’t be handled. You can often handle with a juxtaposition of one or more functions putting them together. You can get back to whatever you need in a case where say it didn’t give you a particular part of a date.

40:37
It would just be a matter of Parson what’s going into the function so could take a left three or whatever and then put it in our four and then put it in year and it would get you know, things like that. So depending on what kind of format your data is in the approach might be different The Source data, but any of that can be done and most of it can be done straightforwardly with the pre-existing function, but I think one of the things that does bring up though is that we always recommend that you leverage a Time Dimension versus trying to do.

41:11
Ooh, all these gymnastics absolute Rule and I think you even mention that without a Time Dimension. There’s capabilities that you can’t take advantage of so do you want to talk about that a little bit? Yeah without a Time Dimension. You can’t do any time intelligence not with power bi and probably not with other different things particularly PowerPivot or certainly sass a lot of times though to you. Come across odd things.

41:38
Like you’ve got a calendar other than a standard de you might want to you know, make sure all that’s in your date table. Like you’ve got the calendar date and the you know, whatever the calendar quarter and whatever the other month or the other quarter is in the other calendar things, like for four five and some of these retail things get extremely complicated, but it can all be done and the table is key to that.

42:04
There’s something and I go through this real briefly, but there’s something a default capability to have power bi handle that stuff for you and you don’t even need a date table because they want people to be able to get to a quick talk start scenario, but you’re seriously limited and you need to really understand the implication of doing that instead of a date table and you can go out and on Google that and understand real quickly what that is this in really, you know what we’re covering today, but it’s something I warm people off of because it defaults to be in left on and if you don’t turn it off your models are going to Just huge because it builds all these date tables to handle every single date. You’ve gotten your data. So you got to be really careful with that. So you’re talking about the time intelligence functionality and power bi don’t know Tom and tell just functionalities.

42:57
What I basically was showing and acts that are you talking about the date table itself or the time intelligence capabilities now, so there’s time intelligence capabilities in DAX and then and then there’s a feature where Try to do like I think like you’re saying where it’ll handle some of that time intelligence automatically for you. And you’re saying that that can be very resource and space intensive and not just give you what yes sire. Yeah, just leaving that own is basically resource and space intensive and it also is unreliable at the current stage with most lots of time intelligence. I want it might give you some of it but your answers aren’t necessarily verifiable. So I would recommend it’s just a best practice to always go.

43:41
Date table anybody will tell you that definitely and so the question of this always that always comes up in my mind is the area of governance and where’s the best place to do a lot of this stuff?

43:57
Because these tools like power bi and tombolo Etc. were born out of this desire for the business to not have to wait for the old-school monolithic bi tools to turn their wheels and spit out the He’s in the and then in the meantime, the business has changed. So the ability to create calculations and models and stuff in the in the desktop tool is great. But then you end up with potentially lots of different versions of the truth and errors and things like that. So, what do you in your experience and your professional opinion? What are some of your what would you say are some of the key ways that people can govern that and manage that in power bi environment?

44:38
Well, the ultimate scale out it’s a great question and one that Everybody gets into pretty quick when they start seeing power bi models replicating all over their organization. But the best way to do it is to get to some kind of semantic layer at a point where all that’s maintained in a central environment something like a tabular model or you know, even a real heavy data warehouse, but say you can’t do that and you’ve got to you know, get moving with what you do have and I understand that certainly I mean it all the time you basically want to have a policy in place.

45:11
Place or some kind of you know rules for doing reports and when people put reports together the best way to do it is what’s called an app workspace and they put it in there and you know, basically there are members of that workspace probably from representative areas, like maybe risk management or Finance accounting Representative members that know how to look at it and tell if it looks correct and or to verify it and at that point they voted up and say, you know, let’s pray.

45:41
Out the S to popular or general usage. So in other words you have data stewards Rider a correction that are subject matter experts in the business areas. And those are the ones that you give the capability in power bi dust certify data sources and things like that. So you avoid that proliferation? Exactly and that’s really important from a couple of perspectives, but that’s something that really needs to happen because it’s dangerous. You’re not worried about gaps.

46:11
statements and all that kind of thing, you know like your financials if your SEC client you’re not worried about that going out because that’ll be taken care of and be super inspected before it’s published to the general public but a lot of times people think this in the Gap, but natural why am I worried about, you know, verifiability and all that a lot of times those internal reports are what, you know, serious decisions are made upon like make or buy all these other things so you can’t necessarily, you know, run your It’s on something that’s unreliable with off-road bi you don’t want everybody doing their own thing. You want to be running it through a central mechanism where it’s approved certified like you said and it’s important. I do like that term off-road bi and tools like this had been kind of on governing.

47:04
You hear the Wild West and that sort of thing and now they’ve they’re starting to bake some of those features in so you’re seeing sort of location of data source and more governments in there and it’s important and that’s what we do with our clients a lot is is you need to constantly be evaluating that environment to and seeing how the business is changing and inspecting those reports for those calculations and measures like you said and consider moving those Upstream into the model and or even further back maybe into your into your data warehouse or that Upstream structure, right?

47:40
We’re at Re-engaged occasionally to and more and more actually to come in where people have done it in kind of a rough shod Manner and got it up and running and they want to go, you know, pretty much enterprise-level with it to come in and do you know an assurance audit or just to just to take a look at you know some of the data governance practices some of the best practices and make sure that you know give you a report back and say these are places you can improve this on and like can be done that way if you don’t know how to do.

48:11
It we can show you how to do it. Well, what do you think are the thing that always kind of pops up next in in these questions and in our minds is the security implications right of you? If you’re if you’re doing this stuff will often times these calculations on and you want to make sure they’re accurate but then if you’re creating these rows and columns you want to make sure that the right data is getting out to the right people. And then where does that security get applied at the workbook level or further Upstream? Can you talk a little bit about some of that?

48:42
Are you talking about something like row-level security are you talking about display? And I’m not quite getting question. Yeah, I mean either really there’s a security ramifications for where you have the data living right there. Basically a lot of ways to secure your data. There are a lot of them they’re baked in you can do at the group level at the app level.

49:04
You can say who has access to things and things of that nature and you can even take it with a concept like Level security which is essentially role-based roll E versus row. The terms are in our changed a lot.

49:19
But there are a couple ways you can even do it with ad you can base it on the 80 namespace and other ways even but you can basically say who can see what and take it to even a granular level at DAX and say, you know using username or something like that in your DAX and it actually performs a lot better than you might expect but you have to look Get all those ramifications before you start plugging it into your calculations and things like that. Whether you really need granule lever. A lot of role-based security is safe. You got five or six people with budgets working in a community to come to their final but you know, you may or may not want people to be able to see certain things.

50:02
Like, you know, what your payroll is going to be next create all kinds of different things like that because that can also obviously cause some Disconcerting reactions and things but there’s a lot of things that you can do with role-based security where people can only see their own Department their own, you know, their division things of that nature. So that’s all been pretty much done very well.

50:28
You can go out and see it all over the web and we actually do a lot of that for people who’ve already gotten up and running and then we go in and help them to implement that security layer what’s is becoming more and more you know, the mainstream Outlet, right and it’s the more you can do that at sort of a group level or versus at the at the PBX or the workbook level. Then you’ll save yourself a lot of administrative hassles.

50:55
It’s even integrated with on Microsoft Office and you know all of that you can you know, use your groups across both products and things of that nature so Well good stuff. What we do have there are some questions in here that are very specific around specific DAX functions, and we try not to get into those in the in the in the QA of the webinars per se since we do cover those in our class and they tend to be a bit of a of a rat hole for the rest of the audience. So we apologize. We don’t we don’t answer those particular questions.

51:32
We will learn in the classes though in the class if you have a specific Singing as time is available. We can often, you know, take that up. Like if we’re discussing a function and somebody wants to say, you know, some further ramifications of it. There’s usually time to take a little bit of that up exactly. So with that that’s those are all the questions that I’m seeing in the questions log, if anybody wants to drop any last questions in there, feel free to otherwise if you want advance to the last slide, please Bill like to thank Mr.

52:04
Pearson here for putting whether a great introductory presentation on DAX, hopefully you understand that. It’s something that is approachable. But is can get fairly complex. It’s very powerful and some of the other, you know, the considerations the various categories and the types and whatnot. And hopefully you got some good information out of this if you have any needs or questions or anything about this, you can reach us at our website or at the email address you see their info. It’s interest.com. Feel free to call us at our Triple Eight number if you like picking up the phone.

52:35
And connect with us on any of our social media sites LinkedIn Slide Share YouTube Twitter and or Facebook. So thank you all very much for joining us today you out there in our audience, and we look forward to seeing you again soon on. Another Senturus’s knowledge series presentation. Have a great one. Bye now. Bye.

Read more