Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
csinha
Frequent Visitor

Convert Excel function to DAX - Power BI (Circular Reference Error)

Hello,
I need help converting this excel sheet to dax in Power BI. 

 

Here are the formulas used in the sheet ( split into two parts):


Formula 1.PNG

 

Formula 2.PNG

 

and finally, here is the Excel sheet with the aforementioned data

Scenario:
This is a fairly typical in finance - debt/loan amortization table, although it is easy to make in excel, I am not able to replicate it in Power BI using DAX. 
I am enountering circular reference errors when I try to replicate these excel functions in Power BI. I think it is due to the fact there are several recursive forumulas in the excel sheet. I'm not sure how to execute them in Power BI. 


Notes:
1. Column A, B, C are the input columns (marked in yellow). 

2. All the other columns are calculated using formlae (marked in blue).

3. The first three of the four input columns are bank statement entries and the remaining fourth one is the daily interest rate. Based on these four columns, we calculate the other columns marked in blue. 


The setup:
I'll do my best to elaboate on the nature of the relationships of the columns in the excel sheet, although it is way easier to follow the functions in the actual attached excel sheet.
1. Column E is dependent on column G, although it references different rows.
2. Column G is the difference of Column F and G.
3. Column G is inturn dependent on Column E.
4. Column F is depenent on Column J and L.
5. Column J in turn needs values from column G, and column L is dependent on column K, which again is inturn dependent on J and column H.
6. Column H uses values from column J and column L.

Here is a Power BI file with the input columns preloaded into the file.

Please help me with this task. I am new to Power BI and DAX and I deliver upon this urgently.

Regards.

 

7 REPLIES 7
csinha
Frequent Visitor

Hey, I believe the data set has self referencing columns rather than recursive functions.
I identified the problem incorrectly.

@v-rzhou-msft @v-sihou-msft I've read other community forum posts where you've addressed a similar issue. Could you take a look at this data set too, you might be able to come up with something.

I open to everything from DAX solutions, to Power Query solutions, or even creative ways to restrcuture the data. 

Please do give it a go, you'd be doing me huge favour!

 

 

Hi @csinha,

For the circular reference issue, you can take a look at the following in to know more about this:

Avoiding circular dependency errors in DAX 

BTW, current power bi reservation calculation can only achieve on the rolling calculations. (apply rolling calculations based on specific index filed and aggregate functions)
AFAIK, power bi data model table does not exist row/column index. If you want to invoke previous calculations, you need to manually/hard code to config correspond filters to get specific row calculations to result and it will increase on each iteration. (it same as Greg_Deckler 's link mentioned)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
rrathod
Frequent Visitor

@amitchandak  Hi Amit,  I have seen a lot of solution provided by you and learned alot from it and we are really thanks for that. Me and my team is stucked at the situation here and it would be really helpfull if you guide us here.

Greg_Deckler
Super User
Super User

@csinha  You cannot do either looping or recursion in DAX. Believe me I have tried. You will need to use Power Query to do the recursion.

Here is a thing on Recursion in DAX - https://www.linkedin.com/pulse/previous-value-recursion-dax-greg-deckler-microsoft-mvp-/

Here is a thing on Recursion in Power Query - https://www.linkedin.com/pulse/fun-graphing-power-bi-part-5-by-5-greg-deckler-microsoft-mvp-/

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hey @Greg_Deckler,
Thanks a lot for replying. I read your post on the Fibonacci Numbers just today, and abolsutely loved it!
Although I do suspect that the issue is recursive functions, I am not sure of it. I think I am way too new to Power BI and DAX to even identify it correctly. 
I'd be extremly grateful if you could you please go through the excel file, you just might find a solution. I'm really desperate here. 

Thanks a ton!

@csinha Thanks, I've been fighting recusion in DAX for forever. I'll try to take a look but could take some time to try to unwind it all and see if there are any "creative" solutions!


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
csinha
Frequent Visitor

I realised the first picture of the sheet didnt make it into the first post. here is sheet I need to replicate in Power BI with DAX.

Full data.PNG

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.