26 Apr DAX Functions in Power BI

In DAX functions in PowerBI, we can create new data from existing data. We can create new data such as aggregated, summarized e.t.c

Now, let’s move to different aspects where we can use our DAX functions. We can use DAX functions in Calculated Columns, Measures, and Tables. We’ll study how we can use DAX in each of the data.

Calculated Columns

A calculated column allows us to create a new column from our data. For instance, let’s say we want to calculate the Discount Percentage of a particular product, whereby in our Data both Discount and List Price data are available. We can use DAX Function to calculate this by using the formula below:

1(Discount/Price) × 100

Measures

We can also create calculations using the measure. We also need to note that Measure calculation works slightly differently from Column calculation. The helpful thing about Measure calculation is that it won’t create an entire column in your data, unlike the calculated column. An example of that is shown below;

1] Discount * Price

Tables

Using DAX functions in tabular model return entire new tables. For example, we can get the state students in a particular school, reside using the function;

1 state = DISTINCT(Students[State])

The Most Commonly Used DAX Functions are:

• SUM, MIN, MAX, AVG
• COUNTROWS, DISTINCTCOUNT
• IF, AND, OR, SWITCH
• ISBLANK, ISFILTERED, ISCROSSFILTERED
• VALUES, ALL, FILTER, CALCULATE
• UNION, INTERSECT, EXCEPT, NATURALINNERJOIN, SUMMARIZECOLUMNS, NATURALLEFTEROUTERJOIN, ISEMPTY
• MEDIAN, GEOMEAN, DATEDIFFPower BI DAX Functions

Average

This DAX function allows us to find the average from a given set of values as shown below;

ItemAvg= AVERAGE(Items[Price])

Concatenate

We can use Concatenate to join values in calculated columns. If you’re working with Measure calculation, you can use the ConcatenateX.

ProMrp = CONCATENATE(Items[name],Items[code])

Max

We can use MAX to calculate the maximum from a given set of values.

HighestItemSold = MAX(Items[Price])

Min

We can use MIN to calculate the minimum from a given set of values.

LowestItemSold = MIN(Items[Price])

Count

Count can also be used to count the number of values in the column. Let’s define a measure to count all sales transactions:

number of sales=COUNT(Items[Sales_Id])

TotalYTD

The TotalYTD function allows us to calculate the sum from the start of the current Year to the specified date.

ourcumlSales = TOTALYTD(SUM(Items[Price]),Invoices[Date])

Thanks for reading this post.