Learn Power Query & Power Pivot with ExcelSujeet (YouTube Blogger)

 1) All() Function:  Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table. This function is not used by itself, but serves as an intermediate function that can be used to change the set of results over which some other calculation is performed.     a) It is used to ignore filters from one or more table.     b)  This function is not used by itself, but serves as an intermediate function.          Ex-1:  CALCULATE([Sales], ALL(DimCustomer)) //DimCustomer is a "Dimension Table"          Ex-2:  CALCULATE([Sales], ALL(DimCustomer), ALL(DimProduct)) //DimCustomer &                                  DimProduct are "Dimension Table"   ...

Data Model in Power Pivot is nothing but relations between two or more tables. Here we create a link between tables using the primary key (from the lookup table, lookup table contains unique records only) & the foreign key (from the fact table, the fact table may contain duplicate records). In excel, we do the same by bringing related columns in one sheet using the Vlookup formula. Difference b/w normal pivot table & power pivot table. Look and feel is the same for both. We may use one data table to create more than one lookup table with unique records. Let say we have one data table with all the information like customer details, product details and product purchased by the customers. With the help of Power Query, we can create 2 lookup tables (customer_details & product_details) and one data table of product sale details.