INTRO TO DAX QUERY VIEW // Write DAX queries EASY using this new view // Beginners Guide to Power BI

in this video we're going to go through the new Dax query view that came out as part of the November updates we're going to go through some basic things like how you can enable it and some of the things that you can do with this new view all of that and more so without further Ado let's get started hi my name is fernan and welcome to the solutions abroad YouTube channel where we cover tips tricks and best practices when working with powerbi I upload new videos every week so make sure hit that subscribe button and the bell icon to get notified when a new one is out so the Dax query view is a new view that is available for you in parbi desktop that lets you write edit and preview your Dax queries similar to how thirdparty tools like tabular editor works and this view is particularly convenient simply because it means that for some basic Dax editing you don't really need to go to the third party tools you can simply just stay within parb desktop to make those Chang changes so if you want to start using the new Dax query view you need to make sure that your parbi desktop is up to dat to the November 2023 version or later so that you can start using it you will find it on the left hand side here as the fourth view from one of these panes now if you are up to date and you still can't see it just need to make sure that it's enabled in the settings menu because it's still a preview feature as of today so to enable that you just need to make sure you go to settings and then go to the preview features and just make sure that this part here Dax Square view is enabled restart your parb desktop and this view should be now available to you so let's go to the actual view itself so here is the Dax Square View and how it looks like let's go through to some of the main components of this view so you have in the middle here the query editor and this is basically what you will use to write or edit your Dax queries so the syntax as you will will notice is basically the same as the one that you will see on the Dax formula bar on the right hand side here is the data pan so this is where you'll see all of your tables your columns and your measures in your model the result section here at the bottom is essentially the preview of the results of your Dax queries when you hit the Run button here on the top looking at the bottom here where you will normally find your pages you have now query Pages here in instead so that means that uh you can create multiple query tabs poking around on your data model you can rename them or organize them and they will be saved as part of your data model so that means that if you make some more queries save them close your desktop file and open it up again they should load up like you left it before we're going to go through it later with an example so I can show you so then we look at the ribbon button at the top here which has just some basic formatting options available for you when you're writing your Dax queries so what we've covered so far seems to be very familiar setup especially if you've used tools like management Studio before however if you haven't used any of those tools before don't worry we're going to go through it together because it's actually not that difficult it's very intuitive and easy to use so first of all just uh to give you some context we are using the subsets of our typical Northwind data set which is a company that sells growth Goods internationally we have some tables and some columns and some measures that we are going to use as part of the demo today it's not too important to know how they're written but just to know that that is the context of our data model so as you'll notice here in our Dax query view when we first opened it it actually created an example code here which is essentially starting with some comments as well as something to evaluate so this one is pretty simple actually so I think it just took one of my tables uh by random but what it's done is it's generated a Dax query that gets the top 100 rows from the products table so it's prefixed by the evaluate which is what you'll need to run your queries from this View and uh let's see what it does when we hit run so when I hit the Run button here you'll see that the results pain at the bottom started to show some results so what it's done is exactly that so it's given us the top 100 entries from that products table so it gives you all of the columns and all of the values in those columns for those top 100 rows so pretty handy if you want to Snoop around and see you know what is in that table now as you all know because this code is basically the Dax code similar to what you're used to it means that to get the same results even before the Dax review you can do this already using calculated tables so if we just copy this for example let's go to our table view let create a new table we going to create this one and call it test I just copied the codes that was in the Dax query View and as you can see it gives us the same result the benefit of having the Dax query view is that it's a lot faster to set up without having to create these temporary tables and it's a more dedicated view to writing Dax code so now let's go back to the Dax query view here and let's have a look at what is available to us at the at the ribbon on the top here so we have a few things like cut copy and paste but on the editing side of things we have a few things here so we have this one format query which as you can see if I click it it simply just creates the indentations and line braks to make your code a little bit easier to read it's especially helpful in sort of the coding environment especially if you have Dax queries that are lines and lines long it just makes it a little bit easier for other developers to read your code we have comment and uncomment here so these just whatever line you have selected it will just comment or add those uh commenting lines here it just gets ignored as you know already and the uncomment does the reverse you can also highlight a few lines like this so similar to how you would expect from a coding IDE so you highlight multiple rows and then hit comment and uncomment which will just make that process a little bit faster for you and then you have your typical ones like find if you're trying to look for a specific keywords again if you have a huge code and you want to find um certain keywords or maybe you want to find and replace you can also do that so again like how you would typically code if you have some naming conventions that you want to change you can use find and replace to kind of easily do that so along with that there is also this command pet here which has a lot of other commands here more advanced commands that you can actually use to make your life a little bit more convenient if you know how to use them so but for now we're not going to go through the command pallet so let's go back to the comments here that the Dax query view has generated for us so it's telling us here that it's saying that here's a sample of the Dax query from your model click run which is what we've done and then on the third line here it says try other Dax queries by right clicking a table column or measure in the data Pane and choosing one from Quick queries so let's have a go at doing that in in one of our tables here so let's look at let's say the order details and let's from this view right click on this data pane so as you can see there is a new option for you on this rightclick menu we have the quick queries and you have a few things here so you have show top 100 show column statistics and a few things here so let's start by clicking the shop show top 100 rows here we go so as you can see what it's done is first of all it's created a second tab here a second query so that means that that your first query is saved on that first Tab and as I mentioned before you can have a lot of queries in the same data model you can rename them and you can keep them saved so that you can work on them later so from this code here as you can see it generated the right Dax query for us that generates the top 100 rows from this order details table so you have your top 100 all of the columns in that table and then it ordered it by order ID ascending and then what it's done is it also ran the query to show the results here so it's giving us already the top 100 rows from the order details table so while these are generated Dax queries that doesn't mean that you can't edit them so you can change what is being shown or even how it's sorted by just modifying and rerunning the query that is generated for you so that's probably the fastest way that you can start getting a Quon yourself with the Dax query view so for example let's say we don't want the discount to show in this result we can just simply delete that from the result here and also maybe we don't want to order it by ID maybe we want to order it by well actually let's order it by order ID but we want to sort it the sending instead so I will just show us the latest orders from our database so if we hit run as you can see it removed the discounts column from our results and it sorted it descending order so it giving us the latest or most recent order orders from our table so looking now at this results pane here at the bottom so obviously you can see the table that is generated to us you can highlight them and you know well actually just scroll through them if you have multiple or if you have a lot of results you can you know go through and paginate through those different results and you can also copy the result that is returned from this Dax Square View and copy it somewhere else like an Excel for examp example however this is what I would typically use for testing purposes if I wanted to check the values that are being returned but not necessarily part of my process let's have a look at some of the other options that is available for you in the quick queries menu so on the same table let's click quick queries and then go show column statistics so if we click on that what it will do is it will just give us all of the columns that are available for you in that I just focus on the results here so what it does is it gives us all of the columns within this table and it gives us some statistics about these columns so how many rows of data there are how many distinct values there are so the typical things that you would get when you're trying to understand the makeup of your data now again you can get all of these stats from the data view but find that having another option which is a little bit quicker is is always good let's look at the other thing that is available for you that is not grade out which is Define all measures in this model now there is the model means everything that you have in your current project in your current report file but if you have well actually let's just go through and click that because the other one is in this table which is grayed out because we don't have any measures in that table and that's because we organize all of our measures usually in our measures table here now if we right click on that you will notice that both of these options are available which is basically the same thing so let's go and Define all the measures in this model so here we are so what it's done is it's created a Define here which just gives us all of the measures that is available to us that is in this model as well as showing us and running those measures to show what the results of those measures are so this is actually a very handy view especially if you're wanting to catalog all of the measures that you're using in your data model so now that we've gone through the options that you have on the tables let's try to rightclick one of the measures here because they have a few other different options available for you so here we have some few options here so here we have a few new options available to us you have the evaluate which if I click here it will simply just show or run the the query for us the Dex query just to show us the results of that if we go and right click and Define and evaluate what it does is it shows us the the syntax that is in that measure so what how it's written as well as you know running that that that query and you have another option here uh which is the Define with references and evaluate so this is an option that I found extremely useful especially if you have measures that are referencing other measures which are referencing other measures so if you have kind of chain measures like that where it can be very difficult to track you know what the actual logic is because it's always depending on on others this actually lets you see that whole Chain by defining all of the references that are being being made in those measures so that you can see them all in one page so just for an example here I've created a measure here which is basically Target Delta which if I just simply Define and evaluate it calculates using the sales it's referencing the sales measure as part of its calculation but is in the sales measure we don't really know unless we go into the sales and then if there are any other references in the sales then we have to go deeper and deeper now with this new Option quick queries Define with references and evaluate in this same view now you can see the definition of the target Delta as well as any other references to other measures so the sales as well is over here perfect now let's have a look at this pretty cool KN thing that I found that you can do from this view so let's say we want to update some of our measures like let's say we want to change this target Delta and change its calculation maybe we want it doesn't make any sense at the moment but uh let's just pretend we want to change something in this expression so let's say we want to change it into let's say 9,000 as you can see as I've written that you can see this new Option here that pops up update model overwrite measure so what it will do is it will save the changes that I made to this measure save it to the model without me having to do that directly into the model itself so if I click that update model now if we go to the Target Delta which is uh let's just go back to this view here let's go to the Target Delta as you can see there we go the changes have been overwritten through the Dax query view so it's really handy for that another thing that you probably would have noticed in this view is the intelligence so as I was writing you know some values for example you will notice that the intelligence work in the same way that it works in the formula bar which is really really handy it's a lot of it's one of the I guess drawbacks of using third party tools is that it doesn't really support intelligence the same way that native Dax editing in powerb desktop does but another thing that is really cool here is that for example we were talking about references earlier and when you hover over measures like this for example as you can see it gives you the definition of those measures as well so it means that for simple references that don't go too deep this gives you a really quick Insight on what those measures actually do without having to switch your views so that's pretty cool and I think that pretty much covers the basic things that you can do with the new da queare revew now I'm sure I have missed something because I've just covered kind of the the kind of basic elements of what you can do with it however if you want to read more and are you know pretty interested about using this and finding out more about the other things that you can do with it I'll leave the link to the full blog post in the description box below thanks for watching as usual give this video a like if you found it useful give it a dislike if you didn't so not to do better for next time ask you questions in the comment section box below so I can help you and you can help others if you like this video we have a patron page where you can support the channel and get exclusive perks like Early Access demo files and credits at the end of these videos thanks again for watching and see you in the next one bye-bye

Channel: Solutions Abroad

Views: 4,766

Keywords: solutions abroad, power bi, powerbi, power bi tutorials, power bi for beginners, beginners guide to power bi, data analytics, dax, data modelling, data visualisation, business intelligence, how to power bi, power bi how to, power bi best practices, power bi tips and tricks, power bi standards, power bi patterns, power bi help, power bi tips, power bi 2023, dax query, dax query view, power bi dax, power bi dax query, power bi dax editor, power bi queries, power bi query

Id: MA2hekxhscU

Length: 16min 15sec (975 seconds)

Published: Mon Jan 15 2024