Power Pivot Principles: The A to Z of DAX Functions – EFFECT

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at EFFECT. 

The EFFECT function

Does DAX sometimes not have the desired EFFECT?  This function returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year.

It employs the following syntax:

It has two [2] arguments in the syntax:

• nominal_rate: this is required and represents the nominal interest rate
• npery: this is required and represents the number of compounding periods per year.

There are a few remarks for this function:

• EFFECT which is short of the effective rate is calculated as follows:

• the npery argument is rounded to the nearest integer
• an error is returned if:
• nominal_rate ≤ 0
• npery < 1
• the EFFECT function is not compatible with Power Pivot and currently it is only compatible with Power BI, SSAS Tabular, Azure AS and SSDT
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Consider a nominal interest rate of 5% and our npery is 12 months.  Therefore, we will write the following DAX code to convert the nominal rate into the effective rate:

We will have the following figure:

This is the effective rate after compounding twelve [12] times a year.

Come back next week for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.