Module 2: The Excel Data Model
“The Excel Data Model … Basic DAX … Explore an Excel Data Model”
Summaries
- Module 2 > The Excel Data Model > Lecture
- Module 2 > Basic DAX > Lecture
Module 2 > The Excel Data Model > Lecture
- The data model, as you can see, is made of a series of tables.
- Every row has the same data type, has a name and there’s no exceptions, it’s a little bit like tables in Excel but more strict.
- In this 37 00:02:16,721 -> 00:02:21,240 case the largest table only has about 60,000 rows but we’ll see in later models with like two million rows.
- In the diagram view in the ribbon and I will just make it a little bit smaller so all the tables will fit and here you’ll see like a diagram of all the tables and this line is connecting them which represent relations.
- Now, so you see the tables and we see the lines which as I said represents relationships.
- Every relationship has a direction, goes from one table that’s in here.
- This FactInternetSales, which is the main table in this model that contains the actual sales transactions has many rows.
- Many rows in the FactInternetSales table.
- The same with customers, many rows in the sales, many transactions the customers have purchased multiple times so it will have multiple rows in the Internet sales and one row representing this customer in the customer table.
- In this example there’s a table called DimGeography which actually represents cities.
- So the same table of customers play the role of one against the five Internet sales and the role of a many against the DimGeography.
- As one, as if it was one table and we will see it in a moment.
- This table has some information about the product that was purchased, the order, date, the customer, the quantity, the list price and the product cost for each unit of product that was purchased.
- Actually I can add calculated columns to this table that use other columns in the table.
- Now this will be now calculated for each row in the table.
- By having these calculated columns, I have completed the data that I need in this table and I can switch back to Excel, I will just, go back to Excel and in Excel I will say insert pivot table.
- So the default for Excel to create a pivot table, or a pivot chart, if there is already a model in this file, is to use the data model as a basis for the pivot.
- This is not like, any other pivot that you’ve seen before because it contains all the tables that were in this model.
- In each one there are multiple columns, I can open it and then start dragging and using columns for any one of this tables.
- Put it on rows and I go to another table here, which is about product categories and I take the product category and drag it too and now we have a full pivot and also I can create in the same way, I can create from, let’s say from the year I want to create a slicer.
- So I had my model with many tables with the relationship.
- The fact that I’m clicking here and I’m filtering on a year, because the year is coming from a table called date, and this table is connected or related to the main table, it means that when I’m clicking here it actually applies the filter to the main table and I see only rows that correspond to the year 2006 in the same way that this number here, for Australia bikes, show just data, the sum of data, for Australia and bikes.
Module 2 > Basic DAX > Lecture
- What’s in it, and that it’s made of tables and relationships.
- We finished by having a pivot table populated from the data model.
- We’ve seen that the data, the field list that’s here on the right side, contained multiple tables and I was able to drag things from each one of the tables, and to create my first pivot.
- It created a value of sum of revenue which is the same behavior as Excel would do with native pivot table from grid data.
- For example, there’s things which are possible here which are not possible with a regular native pivot table.
- You can see that, with the same speed I can immediately calculate these results for any combination of slicers and so on.
- I can, for each one of the columns, I can apply a format.
- From now on every time I use this column, it will be formatted appropriately as a currency.
- In the previous video, we created two calculated columns.
- Now, the syntax for these columns, maybe you wonder, what is the syntax? It looks very much like Excel.
- One is for calculated columns and one is for measures.
- I will say totcost, sum of, and this is sum of the cost column in.
- So once I have the revenue and the cost, I can calculate the margin percent.
- So I’m not referring to columns in the table, but instead I’m referring to other calculated measures that are already created.
- I want just to show you that now, when I go back to the list of columns in this table, the main table, I see the totrev.
- I can still, of course, filter, slice it by year and calculate it every time.
- I just said that this is the sum of, so this is like the margin, but this is one this is the sum of the column revenue.
- The reason it’s giving me a different number is that it’s being calculated fresh every time with a different context.
- It’s not a sum of this column here, it’s actually a new fresh calculation with a new context.
- This is a very, very important principle for the DAX language that we’re going to see, and I will repeat a few times for you to grasp the importance of, that every measure is calculated with a context.
- The context, by default, comes from if it’s used in the pivot, there’s something on columns, something on rows, some slicers, a filter maybe for the pivot and so on.
- Now, what about, let’s switch back for a moment to understand more about the calculated columns.
- When I calculate this calculated column, it doesn’t see the pivot that will eventually be used or built from that.
- You can use any other column in this row of data.
- It could also use columns in other tables, like an extended row.
- The relationships make other columns to be kind of related to this one.
- Let me give you one example to create such a calculated column that is using the context which is called the row context to, let’s go, and for example into the product table.
- In the product table I have this data and I want, let’s say that you saw that I have product category table and product sub category table.
- Those two tables are actually used just for one column each.
- I may have a goal to reduce the number of tables that my user sees when he creates a pivot table.
- In order to do that, I can actually create a column here, in the products table that will have the category name, for example.
- Related means I’m going to use a column from another table but that is related to this one.
- So now I have a category column in the product table.
- When I go back to my pivot table, I will see that the product category table have disappeared.
- If I opened a dimproduct table, I will see that it has now a new column that wasn’t there that is called a category.
- I can use this instead of the original one that came from the category table.
- So I reduce the number of tables and I added a calculated column from a related table.
- We’ve started to see calculated measures using DAX.
- The measures are calculated within a filter context, while the calculated columns are calculated within a row context.
- We’re going to use a lot more of the measures, not so much calculated columns, in subsequent modules.
