Calculated Columns and Measures

http://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Add Data From Current Excel Sheet To Data Table

Select Range in Excel worksheet and  click on add  to Data table.

 

Concept of Row Context and Filter Context in Power BI

Row Context:

DAX expression normally operates on columns like:

GrossProfit:=SUMX(Sales, Sales[Amount]-Sales[TotalCost])

 

*All functions that end in X are iterators.

In above example, you get the value of  Sales[Amount] for currently  iterated row by SUMX()

 

NumofAllProducts := COUNTROWS(Values(Product[ProductName]))

In this example, Product[ProductName] does not retrieve value of ProductName for a specific row; instead column reference is used to get values.

 

The concept of current row in first example of DAX Expression is known as Row Context. You have a  row context whenever you iterate a table. Every iterator introduces a row context and iterators can be nested. For example:

AverageDiscountedSalesPerCustomer :=

AVERAGEX (

    Customer,

    SUMX (

        RELATEDTABLE ( Sales ),

        Sales[SalesAmount] * Customer[DiscountPct]

    )

)

Filter Context:

The filter context is the set of filters applied to the data model before the evaluation of a DAX expression starts. When you use a measure in a pivot table, for example, it produces different results for each cell because the same expression is evaluated over a different subset of the data. The Microsoft documentation describes as query context those filters applied by the user interface of a pivot table and as filter context, those other filters applied by DAX expressions that you can write in a measure. In reality, these filters are almost identical in their effects (the real differences are not important for this introductive article), so we simply defines as filter context the set of filters applied to the evaluation of a DAX expression, usually a measure, regardless of how they have been generated.

                A filter context automatically propagates through relationships, according to the cross filter direction of the relationship. You can control the filter context and its propagation using DAX functions such as CALCULATE, CALCULATETABLE, ALL, VALUES, FILTER, USERELATIONSHIP, and CROSSFILTER.

 

Create a Star Schema Model in Power BI

In order to create a  star schema model from  source data system, one may have to perform some data Denormalization.

Although transactional database system  tend to be highly normalized; reporting systems are denormalized into star schema.

One of the best models to use when analyzing large set of data is  Star Schema.

We’ll create following steps to create Star Schema model:

(We are using Access database Adventureworks)

 

Ø Create table relations:

 

Using Table Import  Wizard in Power Pivot Data Model, connect to database, select tables and fields to import data.(Date, Customer and Internet Sales)

                Tables and selected field lists in imported data:

Source Table                         Friendly Name     Fields

DimDate                                 Date                        DateKey, FullDateAlternateKey, EnglishMonthName,               

                                                                                MonthnumberofYear, CalendarQuarter, CalendarYear

 

DimCustomer                        Customer               CustomerKey, BirthDate, MaritalStatus, Gender, YearlyIncome,

                                                                               TotalChildren,HouseOwner, NumberofCars

FactInternetSales                 Internet Sales       ProductKey,OrderDateKey,ShipDateKey,CustomerKey,     

                                                                               SalesTerritoryKey,SalesOrderNumber,SalesOrderLineNumber,

                                                                               OrderQuantity,UnitPrice,TotalProductCost, SalesAmount

 

Switch the Power Pivot window to diagram view, we’ll see Customer and Internet Sales have a relationship defined.

Now create relationship between DateKey from Data table and OrderDateKey in the Internet Sales table. Similarly create relationship between DateKey  and  ShipDateKey.

 

Ø Denormalize  Data:

On the Home tab of  Power Pivot Model Designer window, click existing connections button. Open connection of

Adventureworks.acdb and select query option for importing data and change query friendly name  to Product.

After verifying that you are getting data, click OK button and Finish button on next screen.

Ø Create a Linked  Table:

To create a linked table, enter data shown below to an empty spread sheet. Convert data to table and rename table name SalesTerritory

 

SalesTerritoryKey	Region	Country	Group
1	Noerthwest	Unied States	North America
2	Northeast	Unied States	North America
3	Central	Unied States	North America
4	Southwest	Unied States	North America
5	Southeast	Unied States	North America
6	Canada	Canada	North America
7	France	France	Europe
8	Germany	Germany	Europe
9	Australia	Australia	Pacific
10	United Kingdom	United Kingdom	Europe
11	NA	NA	NA

 

On the Power Pivot  tab, click Add to Data Model button

Ø Now Create Hierarchy

Creating hierarchies is one way to increase the usability of your model and help users instinctively gain more value in their data analysis. In the next section you will see some other things you can do to the model to increase its usability.

Ø Now Create a Business Model with a Pivot Table

Relate tables in Power Pivot without using relations

RELATED function in Power Pivot is equivalent to  common Vlookup/Hlookup function in pivot table. But the most important factor to note that before using RELATED function in Power Pivot one must create a relationship between fields containing lookup values in both tables.

 

Here we are using 3 tables  from Database AdventureWorkks2008r2

Ø  Product

Ø  ProductCategory

Ø  ProductSubCategory

Now  create two calculated columns in the Product table, and then you hide the product category and subcategory tables from the model. First, create a calculated column in the Product table with these DAX expressions:

 

ProductCategory=Related(ProductCategory[Name])

ProductSubCategory=Related(ProductSubCategory[Name])

 

 

 

At this point there is no need to show ProductCategory and ProductSubCategory  tables in Pivottable  because their content is now  available in Product table. For this reason it is better to hide them from data model. To do so select table in Diagram view, right-click it and choose Hide from Client tools



A Simple report creation using Calulated Field in Data Model

Here we are using 3 tables  from Database AdventureWorkks2008r2

1. Product
2. ProductCategory
3. ProductSubCategory

• Open Power Pivot window, load three tables as discussed in my earlier blog.

(As Relationship already present in database, Power Pivot creates them during loading process)

• Now create a calculated field as we need to count no of products using below mentioned definition:

                      NumofProducts:=CountRows(Product)

 

 

 





• Now simply create a pivot table, put category name from ProductCategory table into rows and choose Calculated field 'NumofProducts' for values.

 

 

 

Advantages of using Data Model:

 

1. Using Data Model Approach, one did not need to use vlookup to create columns. Here Pivottable slice products using category name based on relationship.
2. CalculatedField  created is global for data model. It an be accessed by different Pivottables based on same data model.
3. Regarding speed and memory usage, data model is capable of holding hundreds of millions of rows and computing very complex Pivottables in a matter of seconds.

 



 

Create a Simple Calculated Column in Power Pivot Data Model using DAX (Excel 2013)

We are assuming that your Power Pivot Tab is enabled.

1. Under Power Pivot Tab Click Manage in Data  Model

    2.  A new window pops up(called Power Pivot Data Model); under 'Get External Data Tab' , select SQL Server 'From Database'.

    3. Now connect to SQL Server.




    4. Click Next; then you will have two options either write your own query or get data from selected tables.



   5. Once you click Finish, Power Pivot starts loading data from  selected tables in memory. Once  it finishes, it shows a summary report and returns you to Power Pivot Window which now shows all data loaded into Power Pivot Data Model.
(Here we have selected one single  table SpecialOffer from AdventureWorks2008R2 database and will create a calculated column on no. of  days offer run)

    6. Now go to Design tab of the Ribbon and click Add.

    7. New Column is Added.

    8. Write a DAX expression in formula bar, press  {Enter}. Power Pivot computes values and adds them to a new column(named CalculatedColumn1 by default)

         DAX Formula=1.*(SpecialOffer[EndDate]-SpecialOffer[StartDate])+1

    9. To rename Column name, right click column header and choose rename column.