Basic count function for counting the values in a column and then explain why you need to use a different function to count boolean values we'll explain how blanks influence a.
Result of a count and how you can use a count rows function to avoid problems caused by blanks we'll show you how you can count the unique values in a column and then to.
Finish the video how you count the results of an expression so let's get started to get started i've created a new blank report and as usual the first thing.
We'll do is import some data from an excel workbook we'll use a workbook called movies and as usual i'll drop a link in the video description so you can download this.
File yourself there are just two tables in this workbook at the moment it's a slightly simplified version of the model we've been working with for previous parts of.
That table ourselves once we've loaded all the data so there we go all the information has been imported and we're ready to start creating some measures.
Dax has several functions for counting things and we'll start by looking at the simplest one of those called count which allows you to count the values in a column.
So let's right-click the all measures table and choose to create a new measure and i'm going to call this first one count ids so we'll count the values in the id column of the movies table.
I'll just zoom in a bit so we can see what's going on and change the name of the measure to count ids we'll make this equal to the result of the count function and you can see in.
The tooltip that this function requests a reference to a column name so we'll refer to the movie's id column and then close around brackets now the count function allows you to.
Count values of several different data
Types here we're counting numbers in the id column but you can also count text or dates as well so i'm just going to copy this measure.And then create two more using the exact same pattern we'll count the titles in the title column and then also the release dates so i'll add another new measure to the.
Table and then paste in the code i've just copied we'll call this first one count titles and then change the column name to title.
We'll then create one final measure for the moment we'll call this one count dates paste in the same code change the name and then change the.
Column that we're referencing to release date okay so once we've created those three measures i'm going to create a table visual on the report so we can see.
What's going on so let's insert a table from the visualizations panel i'll add in the genre field so we can group our data a little and then add in the count of ids count of titles and.
Count of dates just do a tiny bit of formatting let's increase the the size of the table first so we can see what's going on and then we'll also use the format pane.
To bump up the font size a little bit let's go up to let's say 13 or so so that's a bit easier to read and now that i've created my measures i can get rid of the delete this column column so.
We can right click on that and choose to delete it from the model confirm that we want to do that and that will make the table into a measures table and there you can see.
That the count function returns the same
Results whether you're counting numbers or text or dates there is one type of data that the count function can't deal with and that's.Boolean or true false values now the movies table doesn't contain any true false values at the moment so let's start by heading into the data view and then we'll add a new calculated column.
To the movies table which will tell us whether a film is an oscar winner or not so i'm going to create a new column and i'm going to call this one is winner.
And then i'm simply going to write a logical test which says oscar wins greater than zero so that statement can either be true or it can be false when i hit enter we'll.
See a list of true false values appearing in that column and the data type will be set to true false now let's try to count the values in that column in a new measure.
If we head back to the report view and then right click the all measures table choose to create a new measure i'll call this one something like count true false.
Equals and then count and then the column name i'll reference is called is winner if i close the round brackets and press enter that's fine but as soon as i try.
To add that measure to a visual in the report we'll find that the visual is broken if i click the c details button we'll get a fairly simple message saying the.
Function count cannot work with values of type boolean if we did want to do this then there's a separate function which allows us to count boolean values the.
Function name is count a the count a function does exactly the same as the count function it just also includes the ability to count true false values.
So if i create my measure with a count a function we'll see once again the exact same list of results using the count a function instead.
Both the count and the count a functions will only count the number of values in a column if there's any row which contains a blank that won't be included in the count.
To demonstrate that let's count the number of budgets for our movies i'm going to add another new measure to the measures table we'll call this one count budgets.
And make it equal to we could use either of the two functions here we could use count or count a as i know the budget column is a numeric column rather than a boolean column i'm.
Going to use the count function i can refer to the budget close around brackets and hit enter and if i insert this into the table you can easily compare the results of.
The count of budget with the count of the other columns which all contain values on each row just to demonstrate that the count a function will do the same thing if i.
Change the count to count a we'll see that the end result is identical so both count and count a ignore blanks if you're trying to count the number of rows in a table and you want to avoid.
The potential to accidentally miscount those rows by counting a column which contains blanks you can use a different function to do that called count rows as the name suggests the count rows.
Function counts the number of rows in a table rather than the number of values in a column and as an added bonus it works more efficiently than count or count a.
So let's right click the all measures table and choose to create a new measure we'll call this one count films or count movies and we'll make this one equal to the.
Count rows function now you'll see this time the tooltip requests a reference to a table rather than a single column in a table so we only have one table we can count it's.
Called movies we can close around brackets and hit enter and we'll see if we add that to our table visual that it has the same result as the count of ids titles and dates and.
The true false values as well so telling us there that none of the id title date or true false column contain any blanks we could also use the count of rows.
Versus the count of budgets to work out how many budgets have been populated so let's create another new measure quickly if we add a new measure we'll call this one percentage of.
Completed budgets or percentage of filled in budgets something along those lines and we can simply make this equal to the result of the divide function.
And i'm going to divide the count of budgets by the count of films we can close around brackets and hit enter.
I'll just apply the percentage formatting to that new measure and then we can add that into our table and we'll see which genres have.
Been fully populated with budgets and which ones are missing some dax also contains functions which count the unique values in a column i know that in my movies database there.
Are multiple films with the same title and i'd like to just count the unique titles that exist to do that let's add a new measure to the all measures table.