Tag Archives: T-DAX

DAX with Tabular Model Series – Many to Many Relationship in Tabular Model, Achieve via DAX

08 Wednesday Jul 2015

Posted by in DAX

≈ Leave a comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


DAX with Tabular Model Series – Many to Many Relationship in Tabular Model, Achieve by DAX

Tabular model is completely different from Multidimensional in terms of storage, programming language or wait works and in the modeling part.

It is based on Relational Modeling. We have tables and relationships. In tabular model we can have one to one and one to many relationships. Analysis Services 2012 Tabular Model do not support many-to-many relationship in the native data model. However, We can achieve many-to-many relationship by writing a complex DAX expression.

In Multi dimensional model we have many to many relationship as a native relationship available in the model. We need to write complex DAX logic in order to achieve many to many relationship.

For calculations we are using calculated columns and defining measures !

I will post a Sample DAX example of how we can achieve many to many relationship via DAX in the next post

That’s all folks; I’ll see you soon with more “DAX” articles.

Thanks!

Pawan Kumar Khowal

MSBISKills.com

DAX with Tabular Model Series – What is a Context in DAX?

08 Wednesday Jul 2015

Posted by in DAX

≈ Leave a comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


DAX with Tabular Model Series – What is a Context in DAX?

Context basically used to perform dynamic analysis, in which the results of a formula can change to reflect the current row selection and also any related data.

Understanding context and using context effectively are critical for building high-performing, dynamic analyses, and for troubleshooting problems in formulas.

Context is also called as Evaluation Context

There are following types of context-

  • Row context
  • Query context
  • Filter context

Filter context is the set of values allowed in each column, based on filter constraints that were applied to the row or that are defined by filter expressions within the formula. In simple terms you can consider filter context as a set of conditions in a WHERE clause of a SQL SELECT statement.

For example, check out a sample SQL statement and Equivalent DAX

SQL

--

SELECT ColorType
FROM Colors
WHERE ColorType <> -1

--

DAX

--

EVALUATE
(
	FILTER
	(
		VALUES('[Colors]'[ColorType]),
		ColorType <> -1
	)
)

--

Pawan Kumar Khowak - DAX with Tabular Model- II(Context)

Check out the picture above – DAX

Evaluate is the keyword from where we should start our query. Values clause is used to return distinct column values. It will simply discard the duplicate values and filter used in this query is used as a where clause used to filter out rows where ColorType <> -1. I will explain Row context and query context in the upcoming posts !

Summary

This is the small information about Context in DAX. I will some posts about how context works and row & query context.

That’s all folks; I hope you’ve enjoyed learning about Context in DAX, and I’ll see you soon with more “DAX” articles.

Thanks!

Pawan Kumar Khowal

MSBISKills.com

DAX with Tabular Model Series – What is DAX (Data Analysis Expressions ), Where it is used ?

30 Monday Mar 2015

Posted by in Analysis Services, DAX

≈ Leave a comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


DAX with Tabular Model Series – What is DAX (Data Analysis Expressions), Where it is used ?

This stairway will contain a series of articles that will expand on the DAX foundation that I have learned over three years. This stairway should help readers prepare for writing, Mastering and Optimizing DAX with Tabular Model

Full form of DAX is Data Analysis Expressions. This language is created by Microsoft to fetch data from Tabular model cubes. This can be used at some more places, that information is given below.

Data Analysis Expressions (DAX) is the formula language used to create custom calculations in Microsoft PowerPivot for Microsoft Excel workbooks and SQL Server 2012 Analysis Services (SSAS) tabular model projects.

DAX formulas include functions, operators, and values to perform advanced calculations on relational tables and columns, including lookups to related values and related tables.

While DAX applies to both PowerPivot workbooks and tabular model projects, this topic applies more specifically to tabular model projects authored in SQL Server Data Tools (SSDT).

Before reading this topic, you should have a good understanding of tabular models and the tabular model project authoring environment in SQL Server Data Tools (SSDT).

Lets learn some basic DAX formulas in comparision with SQL. Today we will see how we can select all the columns from a single table

SQL 

--

Select [all_Columns] from [MonthlySales]

--
DAX 
--

EVALUATE
 (
    MonthlySales
 )

--

As you can see in SQL we have started with a Key word SELECT and in case of DAX our query should start with a key word called “EVALUATE”. MonthlySales is the name of the table in a tabular model cube. Consider EVALUATE as similar to SELECT.

That’s all folks; I hope you’ve enjoyed learning about What is DAX and How to select data from a single table and I’ll see you soon with more “DAX” articles.

Thanks!

Pawan Kumar Khowal

MSBISKills.com