What is the EXP Function in DAX?
The EXP function in DAX is used to calculate the exponential value of a given number. In other words, it returns the mathematical constant e raised to the power of a number. The constant e is approximately equal to 2.71828 and is a fundamental mathematical constant that appears in many areas of mathematics and science.
The syntax for the EXP function is as follows:
Here, ❰number❱ is the exponent to which the constant e is raised. This can be a numeric value, a column reference, or an expression that evaluates to a numeric value.
Using the EXP Function in Power BI
To use the EXP function in Power BI, we need to follow these steps:
1. Open Power BI Desktop and create a new report.
2. Connect to a data source and load the data into the report.
3. Create a new measure by clicking on the "New Measure" button in the "Modeling" tab.
4. In the formula bar, enter the EXP function with the desired exponent. For example, to calculate the exponential value of 2, we can enter the following formula:
ExpValue = EXP(2)
5. Press enter to validate the formula. The new measure will be created and added to the report.
We can now use the new measure in our visualizations to display the exponential value of the given number. For example, we can create a simple column chart that shows the exponential value of each value in a column.
Examples of Using the EXP Function
Let's look at some examples of using the EXP function in Power BI.
Example 1: Calculating Compound Interest
Suppose we want to calculate the compound interest on a principal amount of $10,000 at an annual interest rate of 5% for 5 years. We can use the following formula to calculate the compound interest:
CompoundInterest = PrincipalAmount * EXP(InterestRate * NumberOfYears)
Here, PrincipalAmount is the initial investment, InterestRate is the annual interest rate, and NumberOfYears is the number of years the investment is held.
Using the above formula, we can create a new measure in Power BI that calculates the compound interest as follows:
CompoundInterest = 10000 * EXP(0.05 * 5)
This will give us a value of $12,763.56, which is the compounded value of the initial investment.
Example 2: Calculating Population Growth
Suppose we have a dataset that contains the population of a city for each year from 2010 to 2020. We want to calculate the annual growth rate of the population and the projected population for the next 5 years. We can use the following formula to calculate the annual growth rate:
AnnualGrowthRate = (EXP(LN(CurrentPopulation) – LN(PreviousPopulation)) – 1) * 100
Here, CurrentPopulation is the population for the current year, and PreviousPopulation is the population for the previous year. The LN function is used to calculate the natural logarithm of the population values.
Using the above formula, we can create a new measure in Power BI that calculates the annual growth rate as follows:
AnnualGrowthRate = (EXP(LN(SUM(Population[Population]) – LN(CALCULATE(SUM(Population[Population]), PREVIOUSYEAR(Population[Year])))) – 1) * 100
We can then use this measure in a line chart to visualize the annual population growth rate over the years.
To project the population for the next 5 years, we can use the following formula:
ProjectedPopulation = CurrentPopulation * EXP(AnnualGrowthRate * NumberOfYears)
Here, CurrentPopulation is the population for the current year, AnnualGrowthRate is the annual growth rate, and NumberOfYears is the number of years for which the projection is to be made.
Using the above formula, we can create a new measure in Power BI that calculates the projected population as follows:
ProjectedPopulation = SUM(Population[Population]) * EXP(AnnualGrowthRate * 5)
We can then use this measure in a line chart to visualize the projected population for the next 5 years.
The EXP function in DAX is a powerful tool for calculating exponential values in Power BI. It can be used in a variety of scenarios, such as calculating compound interest, population growth, and more. By following the steps outlined in this article, you can start using the EXP function in your Power BI reports and gain valuable insights from your data.
Power BI DAX Training Courses by G Com Solutions (0800 998 9248)
Power BI DAX Intensive Training Course£1,050.00 – £26,550.00
Power BI DAX Introduction£395.00 – £9,750.00
Power BI DAX Intermediate£395.00 – £9,750.00
Power BI DAX Advanced£395.00 – £9,750.00