NEW! Exploring the SEMANTIC MODEL in Power BI | October 2023 Update

the October 23 version of powerbi is there which lets you see the semantic model which means we can see all different kinds of data items like calculation groups perspectives cultures predition inside of powerbi desktop itself without the need of an external tool and if you have no clue what that means well that's what this video is for let's get [Applause] started welcome to how to power behind my name is boss and if this is the very first time for you visiting this channel make sure to hit that subscribe button to stay up to dat on all of my videos and we just share everything I know about powerbi all right let's explore this semantic model for that we need to be on the October 23 version so double check over here at top and the help about and you see we have October 23 now once you have done that head over to the options which you can do here in the bottom right corner click on settings and here on the left hand side you want to go to preview features and then all the way at the bottom check model Explorer and calculation group authoring now once you have that selected click on okay and then we can head over to the modeling view now what changed now here on the right hand side we have now a toggle that lets us switch between tables which was already there before and the model and that's new so here we can see the semantic model meaning we can see all different kinds of data items like calculation groups cultures measures perspectives relationships roles and tables now let me click this away i' start with what I think is probably the most exciting one which is calculation groups not only can we see them over here we can also create them now what do you need calculation groups for and what do they do now calculation groups they are most often used to reduce the number of measures that you have in your data model let me give you a simple straightfor forward example now here we have measures and you see we have measures like r actuals r actuals last year year over year year over year percentage year to date and then we have similar kind of calculations but then for the forecast right and maybe you have other fields that you want to apply time intelligence calculations to and every time that pattern that you want to apply is the same so that's exactly what calculation groups let you do you just set up all of these time calculations just once and then you can apply them to whatever measure you like now let's see this in practice so I'm going to go over here to calculation groups click on three dots new calculation group or you go here all the way to the top calculation group and then you get this warning when you add a calculation group implicit measures will be discouraged so you cannot really use them anymore this means you will need to create explicit measures which is anyways a good good idea all right so not a big deal let's click on yes and that opens the formula bar and from here we can give our calculation item a name so that is the part that's in front of the equal sign now let's call this one current year all right now current year equals the select and measure all right that's fine that's going to be my first calculation item and just like this I can keep on adding more and more so let's go over here you see we have now calculation items one click on three dots new calculation item let's create the next one so this one I'm going to call last year so last year and over here selected measure we turns the values of well the measure to which you're going to apply the calculation group so here I would like to say I'm going to calculate and I'm going to have as an expression the selected measure to which I'm going to apply the calculation item and then over here for the second part there I want to well use the data add function to shift my dates one year back all right so minus one and the interval is going to be here okay and just like this I'm going to create all of these calculation patterns that I later on want to apply to whatever measure all right so let's do this and now we have all of the calculation items that I want to have like year-over-year we have year-over-year percentage and year-to date now let's give this calculation group also a better name instead of calculation group I'm going to rename that one to time intelligence and the column that contains calculation items I'm going to rename two date calculations now that calculation group basically just shows up as a separate table in your model so if we switch to the data View and then select that calculation group you see it's just a table with two columns the first one contains all the calculation items and over here we have an ordinal column for the Sorting all right now let's go over here to the report View and see this calculation group in action all right now let's try it out on a matrix visual so I'm going to pick the Matrix visual on rows I would like to have breakdown by prod subcategories and then here on values I'm going to pick one of my measures let's go for ref actuals okay now let's make this Matrix a little bit bigger and now I would like to see the current year values for ref actuals last year values year-over year year-over-year percentage and year to- dat values well instead of having now to write all of these measures we have our calculation group time intelligence so let's go over there and let's add the date calculations on columns all right so add data time intelligence date calculations and boom we have all of these calculations applied to the revenue all right and what if we want to do the same thing for forecast well the only thing that you would need to do is go back over here to the bills panel and then replace ref actuals with let's go over here to metrix and pick the ref forecast measure that's it so all of these other measures last year year over year year over year percentage year to date and then having to repeat this again for forecast those you can delete you only need this calculation group all right but now you're looking at this and you probably think yeah but that looks a little bit weird the formatting here for year-over-year percentage that looks definitely off and maybe you want to show these values in millions well for that we have Dynamic formatting four calculation items so for that we need to go back to the modeling View and then select let's say over here the year-over-year percentage all right and here we want to have a dynamic formatting string so so let's toggle that on and then here we have now the drop down that lets us switch between format and item all right and here we are defining the format so we need a formatting string now if you want to know everything about formatting strings then and just check out this video over there all right so we could say I want to have one decimal place so 0.0 percentage press enter that's it and for the other ones let's maybe show them a mli so I'm going to go here to the current year item and then also here Dynamic formatting string I'm going to turn on all right and then here I type in the dynamic the formatting string for Millions now let's copy that over and then let's also apply that formatting string to the other calculation items okay so I just did it for the others as well let's now go back here to the report View and that looks better all right so we can control the formatting as as well all right and all of these otheres we can delete so if I go back now to my modeling View and then select all of the ones that I don't need anymore press delete yep I really want to delete those and then go back to the report view you see everything still works the only thing that I'm using over here is that time intelligence calculation group now another way of using this would be by applying a filter to the visualization so instead of having that calculation group over here on columns let me take it out okay you can also either use that calculation group here on filters okay or you create a slicer let me resize it and then put it all the way at the top and then here I'm going to add that calculation group okay now let's resize it again a little bit all right and reposition it so now I'm going to click here on current year that shows me the absolute values for the current year year switch to last year values year over year year over year percentage year to date all right so in this way you can also apply the calculation items to the measure that you have added here to the visualization all right perfect okay so that covers it so we have already seen calculation groups but there was much more let's explore that as well let's go back to the modeling View and right below calculation groups you find cultures now cultures what is that actually well it says we have one e NGB so English Great Britain now this is linked to when you go to settings so here in the report view in the bottom right corner click on settings and here we can go to Regional settings and there you find model language language used when comparing strings and data and for creating internal date fields this only applies when a report is first created and cannot be changed on existing reports all right so for this report we cannot change it anymore however if I'm going to create another report then I want the the model language to be maybe Dutch Netherlands so let's try this out so I'm going to click on okay well might require restart well let's see I'm going to click here on file new report all right there it is completely empty however when I go here to The moning View and then open up the model cultures open it up it now shows NL NL for the Netherlands all right so that is the culture however what what can you really do with it what does it really impact well this plays a big role when we publish this to the service and we want to for example have translations of different measures and Fields that we use in the report well let me show you what I mean now let's first go back to the PBI file from before then go to the modeling View and then here in our semantic model we see cult ASB now BBI told us we cannot change it after we created the file however there's still some ways to do that and add other cultures do it however for that we need da editor why why do we need da editor didn't this just come to powerbi can't we do it from here well not at the moment we can only see it here and to create another culture for translation purposes we need to be in Ted now at least the tool is free however so you can install tablet editor 2 that's the free version TBL editor it's a little bit nicer but you have to pay for it okay so let's open up that will add it to two and here you see also that semantic model data sources perspective relationships roles shared Expressions table translations that's the interesting part for now because that is the culture here NGB so English Great Britain now if I would like to add more translations then I can just right click say new translation now I would like to have one for the Netherland so I'm going to scroll down now there it is Select it click okay so now that we have that translation add it what's next well now that we have it we can go here to our tables and let's pick FD sales and then here let's say we want to have sales amount and for sales amount well we want to put in the translation right so then we can go here to translated names in the property Spain open it up and then here we can say for Dutch I want to show the Dutch name omand and then English well English we don't really need to translate but let's put in sales all right now let's do one more let's also go here to the DM store table and here we have store name and in Dutch that's called Winkle okay and for English well that is just store okay so just like this we can put in the translations then save it back to a model and then let's go back to a powerbi file and here on the cultures we have NGB NL NL two CES okay that's nice but what can you do with it well let's see this in action I'm going to go here to my report View and let's add over here simple visual like a bar chart and here I would like to have on the Y AIS I would like to have store store name all right and on the xaxis I would like to have the measure breath actuals okay now let's make it a little bit bigger just like this and now we have to publish it okay so I'm going to publish it and there we are in powerbi service and pay attention to the title ref actuals by store name okay now I'm going to change the language settings so here and the settings General then here we can choose the language we want to have as our display language so I'm going to click on this and I'm going to switch to touch all right so nowland there it is click on select all right and now I'm going to go back to our report click on refresh and now it says rev actuals back Winkle back Winkle that is now done right and that is the part that I translated before now rev actuals I didn't translate yet but probably a good idea to do that as well okay so now we talked about CEs let's see what else is there in that semantic model okay so we are back here in the modeling view go here to model and right below CES there we have measures measures well measures doesn't need explanation however right below that we find perspectives now perspectives are super helpful when you have a big data model and you just want to well show a subset of that a perspective and this is most often used with personalized visuals all right now let me show you how that works now first of all to make use of personalized visuals we need to go to settings all right so report view let's open the settings again and then here we have report settings all the way at the button then scroll down and here we have personalize visuals allow report to personalize visuals okay so that's checked then we have to go to the format settings of this report where we have also personalized visual so nothing is selected then here formatting and here we can also turn it on personalized visuals now if we click over here on this dropdown then we have over here the whole model but we can add further perspectives all right now at the moment there's no perspective yet as you have seen over here in the semantic model view zero perspectives Now to create one again we cannot do it here in powerbi at least for the time being and we need to have a tool like double editor so I'm going to open that up again and then here you see we have perspectives right click New Perspective and let's call this one subset and here we can add all of the tables columns that we want to have in that subset right so for example dim store I want to have the whole table in there so show in perspectives which one subset set all right now then for channel I would like to have only the channel name so right click sh perspectives subset and just like this we can add all of the tabls that we want to have in that perspective so show in subset and once you're done then we just have to save it back to our model and that's it now let's go then to our BBI file and now here in the modeling View and the model there we have our perspective subset okay all right now let's see that perspective in action I'm going to go back to the report View and then here nothing is selected the formatting pan is open and then all the way at the bottom there we have personalized visuals on and the perspective there it is subset now sometimes I experience that the perspective that I created wasn't there straight away so it was in the modeling view but not here and then I have to save the file restart it before it then finally showed all right now just that let you know now over here with that subset perspective what can we do well personalized visual on we have the perspective that is applied now you might want to apply to all pages that's up to you and once you have this we'll have to republish it and then see how Works action because personalized visuals that's something that we can use in powerbi service now here we are again in powerbi service and let's say you're the business user and you see this chart and you want to make a slight adjustment to it because your preferences Maybe a little bit different from everybody else's well that's exactly where person Lin visuals come in so you hover over it everything is already turned on by the developer so we have the option to personalize this visual by clicking on this button and well everything is still in DCH from before so let me change that quickly okay now it's in English now you see we can change things like the entire charts we can change the x-axis the column y AIS we can add a line Y AIS we can basically do all of these things so maybe maybe I want to have just a table right so or a matrix then I switch to a matrix because well I just want to see everything in a table or Matrix format so that I see the exact values now where does that perspective come in well if I want to add Now new columns let's say so go here to add new columns then I don't see the whole model I only see well the tables and the columns that I added to the model so I can go over here add my metrix I can go over here to dim store where I have all of the columns but from themm Channel I just chose one column the channel name right and the channel name I might want to add on columns just like this okay but that's it so all of the other stuff that's maybe not relevant for me all of these other tables are not there in that perspective and I cannot choose anything from it now a perspective is not a security feature it's just what to make this experience a little bit better to hide everything that's not relevant so you see perspectives together with personalized visuals amazing but we are not done just yet let's go back to the modeling View and we go to the semantic model and here we see calculation groups covered cultures covered measures covered perspectives covered relationships well relationships we didn't talk about just yet but it gives you a nice overview of all of the relationships that you have in your model and just by clicking on it well then we can jump to the property pain to make further adjustment all right so a nice experience now right below that we find roles what roles security roles and that you can set up over here at the Top If you go to manage roles now here we can set up a new role so let's click on manage and let's say somebody is responsible for computers and computers that is a product category so let's go to them product and there I would like to add a new role so this is going to be prod category equals computers let's click here and save that's my security role everything has successfully been applied let's click here on close and now you will find here under roles there you go computers okay so we can see the role and also over here FX is applied to the table di product okay so all of the security ruls will nicely pop up over here now what can you do with it also this is something that you can use them in powerbi service right so after you publish the file then in powerbi service you go to that file which is for me over here click on the three dots for more options and then here security now there you see our roll and you can start assigning that R to people so I'm going to assign that role let's say to me all right there is me and maybe we also want to add a role to the finance department and HR all right and then click on that save it all right and now we can see how our report would look like being one of these people like me or inside of the finance or HR department so that's his role now let's switch to that example from before you see over here only sub categories for that category all right so only the computers category and let's go back to the semantic model again well you might think are we not done yet almost however one more important group is left and that is tables sounds obvious however let's have a closer look because if we let's say open up one of the tables like d dat you see we have eight columns all right we have zero High Keys now what is a high Key Well High key pretty easy to set up let's say we want to have a calendar High key then we go to year click on three dots create high key and that's it right so now we can give it a different name this is going to be our calendar highy and now we can just drag and drop all of the fields that we want to add to it for example the quarter we can add to it and the month and if we want to make adjustments to that hierar key at any point we just have to select it go to the property pain and then over here you can adjust it okay so that's what high keys are now right below that we have measures now in this table we don't have measures we have measures however in the metrix table so let's go there you see over here we have zero columns zero Hier keys but two measures ref actuals ref forecast okay and then we have protections what is protetion well prote you often use in combination with incremental refresh or for purposes of optimizing the refresh time now let's try it out on incremental refresh now for an incremental refresh we first of all have to go to Power query so let's do that first let's open up power query by clicking on transform data here we need two parameters range start range end so let's do that first which we you can do by clicking on manage parameters and then well manage parameters again and let's create the first one so this is going to be range start and then over here type that's date time needs to be date time all right and then over here any value is fine and the current value let's put it two 01 01 2020 okay let's do this one more time so let's click here on new then we have range and all right now here the same so date time any value current value let's go for 01 01 2021 okay now with these two parameters in place then we have to go to the table on which we want to apply incremental refreshes right that's this one you have to make sure that it's not just date but date time okay and then we can well set up a filter so let's click here on a drop down and then date and time filter and then here after now let's make that is after or equal to here we can change to a parameter range start for the second one we want to have the parameter parameter range and so it needs to be before all right so everything is set up done let's click here on close and apply okay so in the model view I'm going to go to the table on which I want to apply the incremental refresh so that was FD Revenue see at the moment there's just one partition all right let's click here on increment refresh and here we can first of all select the table FD Revenue that's already clear done turn it on and then here we can say start archiving data since well five years ago so archiving data starting five years before the refresh date incrementally refresh data starting maybe one day before refresh date all right choose optional settings well the last part you could make direct query so that you have the benefits of import mode and direct query so import mode for all of the old data the archive data and direct query for all of the well recent data so you have the benefit from data freshness that direct query offers but the benefit of better performance that um import mode offers all right so the Best of Both Worlds and here you see nicely visualized at the bottom okay so this is all set up now let let's click you on apply and at this point well partitions is still just one shouldn't there be more well not yet for that we first need to publish it that it needs to be in a premium workspace something to keep in mind so the report and data set is published however for these partitions to be created you first need to refresh the data set so let's do that so I'm going to go over here hit that refresh button all right now the data set is refreshed now let's see if we can download the file so here under three dots then download this file doesn't work because well there's incremental refresh applied to it so I was hoping that we could open it up in power behind then see the number of petitions but that's not possible so that means we still do need double editor sometimes so here in the fre dots workspace settings then we can go here to other uh I mean premium all the way down there we have the xmla end point copy it then go to tablet headitor here would like to make the connection to that endpoint so let's do that over here click okay and then just log in now here we have that database version one all right click on okay and now we can see the number of petitions when we go to tables now let's then open the revenue table partitions and T here we have all of the partitions created now compare that to what we had before in the powerbi desktop file now let's go there before it looked like this partitions and then just one partition all right now these partitions there to make refreshes more optimized or quicker using increment refresh that's the US usual use case all right we covered most of the data items that you find in the semantic model and that you can now see in powerbi desktop from the October 23 version onwards now let me know your questions comments in the comment section below I hope that you like this video and if you did then make sure to also check out these two over here thank you for watching I hope to see you in the next video

Channel: How to Power BI

Views: 54,276

Keywords: powerbi, power bi, datatraining, bas dohmen, power bi update, powerbi october 2023, calculations groups, dax, measures, perspectives, personalize visuals, personalized visuals, cultures powerbi, powerbi translation, hierarchies, partitions, semantic model, semantic model powerbi, power bi model explorer, model explorer

Id: LIbPQfACfKY

Length: 27min 29sec (1649 seconds)

Published: Wed Oct 18 2023