Table Manipulation DAX Functions

ADDCOLUMNS is DAX aggregation function, used to Adds calculated columns to the given table or table
expression.
It’s comes under Table Manipulation DAX Functions category. It will return a table with all its original
columns and the added ones.
Syntax:
ADDCOLUMNS(<table>, <name>, <expression>[ <name>, <expression>]…)
So, Let’s start with an example, You can download the sample Dataset from below link
SuperStoreUS-2015.xlxs

DAX function – AddColumns Sample Dataset

Step 1: Go to Modeling tab and click on New Table

Create New Table – Power Bi

Step 2:  After that, one DAX formula screen will appears , here we will write DAX formula
for ADDCOLUMNS function.
So now, we will multiply ‘Unit Price‘ by 1000 and store into new column ‘TotalPrice‘.
Orders_New = ADDCOLUMNS(Orders, "TotalPrice", Orders[Unit Price]*1000)Copy
Step 3: After that click to commit, it will create new table as name Orders_New and under that table you
will see TotalPrice new column, result as below.
DAX – SUMMARIZE function
Returns a summary table for the requested totals over a set of groups. Its comes under Table Manipulation DAX
Functions category.
Syntax:
SUMMARIZE (<table>, <groupBy_columnName>, <groupBy_columnName> …, <name>, <expression> …)
Step 1: Go to Modeling tab and click to Table

Create table
Step 2: DAX for Summarize table

Summarize Table =

SUMMARIZE(Orders,--- Table Name

Orders[Region], Orders[Product Category],--- Group by columns name
"Total Sale", SUM(Orders[Sales]),--- New column name with expression
"Total Profit", SUM(Orders[Profit]),--- New column name with expression
"Total Discount", SUM(Orders[Discount]),--- New column name with expression
"Total UnitPrice", SUM(Orders[Unit Price])--- New column name with expression
 )Copy
Output

Summarize Table Output

Now you can perform other operations with this summarize table
East Sale = CALCULATE(
SUM('Summarize Table'[Total Sale]),
FILTER('Summarize Table', 'Summarize Table'[Region]="East"))Copy
Summarize DAX with Filter
Summarize Table with filter =

SUMMARIZE(Orders,

Orders[Region], Orders[Product Category],

"Total Sale", SUMX(FILTER(Orders, Orders[Region] in {"East","Central"}), Orders[Sales]),

"Total Profit", SUM(Orders[Profit]),

"Total Discount", SUM(Orders[Discount]),

"Total UnitPrice", SUM(Orders[Unit Price])

DAX – GROUPBY Function

Returns a table with a set of selected columns. Its comes under Table Manipulation DAX Functions
category.
GROUP BY permits DAX CURRENTGROUP function to be used inside aggregation functions in the
extension columns that it adds.
It attempts to reuse the data that has been grouped making it highly performant.

DAX GROUPBY function is similar to DAX SUMMARIZE function. However, GROUPBY does not do
an implicit CALCULATE for any extension columns that it adds.
Syntax:
GROUPBY (<table>, [<groupBy_columnName1>], [<name>, <expression>]… )
Note:
1. The expression used in GroupBy may include any of the “X” aggregation functions, such as SUMX,
AVERAGEX, MINX, MAXX, etc.
2. DAX CALCULATE function and calculated fields are not allowed in the expression.
3. groupBy_columnName must be either in table or in a related table.
4. Each name must be enclosed in double quotation marks.
5. This function is not supported for use in DirectQuery mode when used in calculated columns or row-
level security (RLS) rules.
6. CURRENTGROUP function can only be used in an expression that defines a column within the
GROUPBY function.
So, Let’s start with an example,  you can download the sample Dataset from below link
o SuperStoreUS-2015.xlxs
Create new table, Go to Modeling tab and click to Table

Create table

GROUPBY does not support Calculate:-

GroupByTable =
GROUPBY(Orders, Orders[Region], Orders[Product Category],
"Sale", CALCULATE(SUM(Orders[Sales])),
"Profit", CALCULATE(SUM(Orders[Profit])))Copy

GroupByDAX
It supports only “X” aggregations function with CURRENTGROUP DAX:-
GroupByTable =
GROUPBY(Orders, Orders[Region], Orders[Product Category],
"Sale", SUMX(CURRENTGROUP(),Orders[Sales]),
"Profit", SUMX(CURRENTGROUP(),Orders[Profit]))Copy
 GroupByDaxCurrentGroup
Working of Nested GROUPBY:-
It will return MAX Product Category Sales region wise.
GroupByTable =
GROUPBY(
GROUPBY(Orders, Orders[Region], Orders[Product Category],
"TotalSale", SUMX(CURRENTGROUP(),Orders[Sales])
),
Orders[Region],
"MAX Sales", MAXX(CURRENTGROUP(),[TotalSale]))Copy

NestedGroupBY
GROUPBY with Filter:-
GroupByTable =
FILTER(
GROUPBY(Orders, Orders[Region], Orders[Product Category],
"Sale", SUMX(CURRENTGROUP(),Orders[Sales]),
"Profit", SUMX(CURRENTGROUP(),Orders[Profit]))),
Orders[Region]="Central")Copy
 GroupByDAXWithFilter
GROUPBY with ADDCOLUMNS, support CALCULATE:-
GroupByTableWithAddColumns =
ADDCOLUMNS(
GROUPBY(Orders,
Orders[Region],
Orders[Product Category]),
"Sale", CALCULATE(SUM(Orders[Sales])),
"Profit", CALCULATE(SUM(Orders[Profit]))
)

DAX – DATATABLE Function

 by Power BI Docs
 1 Comment
 DAX

Datatable is a DAX function, using this function you can create Static Dataset/ Table in Power BI,
that cannot be refreshed but you can modify it.
Syntax:
DATATABLE (column1, datatype1,
coulmn2, datatype2,
{
{value1, value2},
{value3, value4 }
}
)Copy
Power BI Data Types as below:
o BOOLEAN (True/False)
o CURRENCY (Fixed Decimal Number)
o DATETIME (Date/Time)
o DOUBLE (Decimal Number)
o INTEGER (Whole Number)
o STRING (Text)
Let’s start with an example
Step-1: Go to Modeling tab and click on New table

Power Bi Modeling- New table

Step-2: After click one DAX formula screen appears, write DAX formula here for static table and
press enter key.

Sales_Range_Datatable =

DATATABLE (

"Sales Range", STRING,

"Min Sales", INTEGER,

"Max Sales", INTEGER,

{ "Low", 0, 1000 },

{ "Medium", 1001, 10000 },

{ "High", 10001, 50000 }

)Copy
Step-3: Table created successfully.

Step-4: Final output of table.

 DataTable DAX function
How we can Modify or Add  DATATABLE records?
o Double click on Datatable dataset name
o Then DAX formula screen appears, change existing values or add new rows & columns
o Then press enter key
Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are
always welcome or you can leave us message on our contact form , we will revert to you asap.

DAX – UNION function

 by Power BI Docs
 DAX

UNION is a Power BI DAX function, it is used to combine two or more tables rows. Its comes under
Table Manipulation DAX Functions category.
Refer similar DAX Post –  EXCEPT,  INTERSECT
Points to be remember:
o The tables should be same number of columns.
o Columns are combined by position in their respective tables.
o The column names in the return table will match the column names in the first Table.
o It returns the all tables rows, including duplicate rows.
Syntax:
UNION ( <Table 1>, <Table 2> …. ,<table N>)Copy
Description:
S no. Parameter Description

1 table name A table that will participate in the union and it is repeatable.

Let’s start with an example

Step 1: Two sample tables with data as below
Step 2: Go to Modeling  Tab, click on New Table

How to take New Table in Power Bi

Step 3: After that one DAX formula screen appears, write below query there
Table_1 : First table name
Table_2: Second table name
Union_Result = UNION(Table_1,Table_2)Copy

Union Query

EXCEPT DAX function is used to returns the rows of one table which do not present in another
table. Its comes under Table Manipulation DAX Functions category.
Syntax:
EXCEPT(<table_expression1>, <table_expression2>Copy
Refer similar DAX Post –  INTERSECT,  UNION
Description:
 S no. Parameter Description

1 Table_expression Any DAX expression that returns a table.

Note:
o The two tables must have the same number of columns.
o Columns are compared based on positioning.
o This function is not supported for use in Direct Query mode.
Let’s use these two tables for practical-
Table-1
ID Product Price

1 Bike 50000

2 Car 400000

Table-2
ID Product Price

1 Bike 50000

2 Car 400000

Follow these steps-

Step-1: Create a new table- Go to Modeling tab and click on table icon.

Create table
Step-2: DAX formula screen appears, write DAX formula here and press enter key.
Except = EXCEPT('Table-1', 'Table-2')Copy

EXCEPT DAX
Step-3: You can see the new created table under fields, and it will return the rows of Table-
1 which do not appear in Table-2.
 EXCEPT DAX Output

Now change the Table sequence under EXCEPT DAX function-

Except DAX = EXCEPT('Table-2', 'Table-1')
Copy

EXCEPT-DAX
See the output, it will return the rows of Table-2 which do not appear in Table-1.

EXCEPT – DAX Output-2

NaturalInnerJoin and NaturalLeftOuterJoin DAX Functions

 by Power BI Docs
 DAX
 NATURALINNERJOIN & NATURALLEFTOUTERJOIN both DAX functions are used to perform the
joins between two tables, these functions comes under Table manipulation functions category.
Common Usage note for both functions:
o Columns being joined on must have the same data type in both tables.
o These functions does not support Direct Query mode.
o The names of the columns that define the relationship need to be different.
Consider the following two tables-
Table: Product
Id Product Qty

1 Bike 30

2 Car 12

3 AC 3

4 Bus 20

Table: Price
Item Price

Bike 60000

Bus 1500000

AC 50000

Car 500000

Truck 2500000

Lights 3000

Create a relationship between both tables-

 Relationship

-NATURALINNERJOIN
Performs an inner join of a table with another table. The tables are joined on common columns (by
name) in the two tables.
The NATURALINNERJOIN function joins the left table with right table using the Inner join
semantics.
This function returns a table with matching values in both tables.
Syntax:
NATURALINNERJOIN(<leftJoinTable>, <rightJoinTable>)Copy
Parameters:
leftJoinTable: A table expression defining the table on the left side of the join.
rightJoinTable: A table expression defining the table on the right side of the join.
Follow these steps in order to implement NATURALINNERJOIN-
Step-1: Go to the Modeling tab > click on create a new table icon.

Create a new table in Power BI

Step-2: Now write a DAX function for inner join-
InnerJoin = NATURALINNERJOIN('Product', 'Price')Copy
Step-3: Now you can see the inner join result, returns new table with matching values in both
tables.
 NaturalInnerJoin DAX

-NATURALLEFTOUTERJOIN
Performs an inner join of a table with another table. The tables are joined on common columns (by
name) in the two tables.
The NATURALLEFTOUTERJOIN function joins the left table with right table using the left outer join
semantics.
This function returns all records from the left table (table1), and the matched records from the
right table (table2).
Syntax:
NATURALLEFTOUTERJOIN(<leftJoinTable>, <rightJoinTable>)Copy
Parameters:
leftJoinTable: A table expression defining the table on the left side of the join.
rightJoinTable: A table expression defining the table on the right side of the join.

Follow these steps in order to implement NATURALLEFTOUTERJOIN –

Step-1: Go to the Modeling tab > click on create a new table icon.
Step-2: Now write a DAX function for inner join-
LeftOuterJoin = NATURALLEFTOUTERJOIN('Price', 'Product')Copy

NaturalLeftOuterJoin DAX
You can see in above screen shot it returns all records from the left table, and the matched records
from the right table.
INTERSECT DAX Function in Power BI
 by Power BI Docs
 DAX
 INTERSECT DAX function – compares two tables and returns common rows. The output of
INTERSECT function is a table with the common rows. Its comes under Table Manipulation DAX
Functions category.
Syntax:
INTERSECT(<table_expression1>, <table_expression2>Copy
Refer similar DAX Post –  EXCEPT,  UNION
Description:
S no. Parameter Description

1 Table_expression Any DAX expression that returns a table.

Note:
o A table that contains all the rows in table_expression1 that are also in table_expression2
o The two tables must have the same number of columns.
o Columns are compared based on positioning.
o This function is not supported for use in Direct Query mode.

Let’s use these two tables for practical-

Table-1
ID Product Price

1 Bike 50000

2 Car 400000

3 Cycle 15000

4 Bus 2500000

5 Truck 3500000
Table-2
ID Product Price

1 Bike 50000
 2 Car 400000

5 Truck 3500000

6 AC 500000

7 Fan 5000
 

Follow these steps-

Step-1: Create a new table- Go to Modeling tab and click on table icon.

Create table
Step-2: DAX formula screen appears, write DAX formula here and press enter key.
Intersect DAX= INTERSECT('Table-1', 'Table-2')
Copy

Intersect DAX
Step-3: You can see the new created table under fields, and it will return the common rows
of Table-1 and Table-2.

Intersect DAX Output

Relationship Functions

DAX USERELATIONSHIP Function

 by Power BI Docs
 DAX
 This DAX function is used to specifies the relationship to be used in a specific calculation as the
one that exists between columnName1 and columnName2.
In simple words, it help us to make a relationship between inactive relationship columns. It’s comes
under Relationship functions DAX category.
Syntax
USERELATIONSHIP(<columnName1>,<columnName2>)Copy

Description
columnName1: A fully qualified name of a column that represents the many side of the
relationship to be used.
columnName2: A fully qualified name of a column that represents one side or lookup side of the
relationship to be used.

Download sample dataset:

Download sample Dataset – SuperStoreUS-2015.xlxs and import into Power BI desktop.
Understand the Requirement
How you will count month wise orders & ship orders, same like below screenshot. The challenge is
here, which date column you will use to display month & calculation for counts?
o If you used Order Date then it will return correct count for Orders, not for Ship.
o If you used Ship Date then it will return correct count for orders shipped, not for Orders.
 Matrix Visual Power BI

Understand the Relationship

In order to solve this situation we created a calendar table and after that we made a relationship
between both dates(Order date & Ship date) with Calendar Date.
You can see the relationship in below screen shot but here you can active one relationship at a
time. You can see relationship with solid line(Order date to Calendar date) is Active and dotted line
relationship(Ship date to Calendar date) is an Inactive.

Active Inactive Relationship

That’s why USERELATIONSHIP DAX comes on role and it will help us to make an Inactive

relationship column to Active.
Let’s get started-
Step-1: Load Orders dataset into Power BI.
Step-2: Create a calendar table using CALENDARAUTO Dax function.
Go to modeling tab > click on table and write below DAX-
Calendar = CALENDARAUTO()Copy
 Create table
Step-3: Now create a relationship between Order date & Ship date to Calendar Date.
Click on Model Tab > Drag Order date to Calendar Date > Drag Ship Date to Calendar Date

Create a Relationship
Step-4: Now create a measure to count the number of orders shipped, here no need to create a
measure for Orders count because Order date relationship is Active with Calendar date.
Count of Ship =
CALCULATE (
COUNT ( Orders[Ship Date] ),
USERELATIONSHIP ( Orders[Ship Date], 'Calendar'[Date] )

)Copy
Step-5: Now add one Matrix visual in Power BI report page and drag some fields with measure.
Rows: Drag Calendar dataset Month column.
Values: Drag Order Date then right click on Order date and select count. And drag “count of ship”
measure.
 Display Active Inactive Relationship in Matrix
So, you can saw above screenshot how we active the “Ship date” column relationship with calendar
date and got the result.

Note:
o This function is used to identifying relationships by their ending point columns.
o we can join Table A and Table B in a calculation, each relationship must be indicated in a different by
using this function.
o This function can’t be used when row-level security is defined for the table in which the measure is
included. It can only be used in DAX functions that take a filter as a parameter. For example, CALCULATE,
CALCULATETABLE, CLOSINGBALANCEMONTH, CLOSINGBALANCEQUARTER, CLOSINGBALANCEYEAR,
TOTALMTD, TOTALQTD and TOTALYTD functions.
o Up to 10 nested function can be used with USERELATIONSHIP functions.
Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are
always welcome or you can leave us message on our contact form , we will revert to you asap.

DAX RELATEDTABLE Function

 by Power BI Docs
 Power BI, DAX
 DAX RelatedTable  function is use to evaluates a table expression in a context modified by the
given filters. It is comes under Relationship functions category.
Syntax:
RELATEDTABLE(<tableName>)
Copy

Description:
tableName – The name of an existing table using standard DAX syntax. It cannot be an expression.
For RelatedTable function relationship between two tables should be:
o One-to-One Relationship
o Many-to-One Relationship, new column or measure can only be defined on the many side of the
relationship

Two tables sample Dataset as below-

Orders Table
OrderId Product Qty

1 Bike 2

2 Cycle 1

3 Cooler 4

Product Table
Product

Bike

AC

Cooler

Cycle

Understand the Requirement-

Do the sum of products quantity from orders table and add one column in Product table and
display the quantity over there.
Let’s get started-
Step-1: Relationship should be important for this, let’s create a relationship between both tables.
Click on data tab > and make relationship based on Product column in both tables.
 Relationship-Power BI
Step-2: Now create a new column in Product table. Right click on Product table and click on New
column.

New column
Step-3: Now write below Dax.
Qty = SUMX(RELATEDTABLE(Orders), Orders[Qty])
Copy
As you can see here we created a column inside Product table and RelatedTable returns
a Orders table value here.

Step-4: See the final output-

Dataset Output