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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lazarus1907
Helper I
Helper I

Power BI as a spreadsheet - Recusrive limitations

Hi all,

 

I know that Power BI is not a Spreadsheet and is not designed to do the same things, but I was wondering whether it is feasable at all to try to use Power BI for recursive calculations, where every new value is based on the preceding one, for example.

To illustrate what I mean, I've included below a made-up example:

borra.png

What I'm really trying to achieve is a bit more complex, but the idea is the same: in order to get the value for cell B2, I need A2, but in order to get B3, I need both A3 and B2 (the value above B3), for B4 I need both A4 and B3 etc. Obviously, the only source data would be column N1, and N2 is what we are trying to calculate. Is this something that can be done at all for a relatively large list of numbers? M or DAX, it makes no difference, as long as it works.

Thanks in advance.

1 ACCEPTED SOLUTION
camargos88
Community Champion
Community Champion

Hi @lazarus1907 ,

 

Check the attached file, it's a power query solution.

Capture.PNG



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

5 REPLIES 5
camargos88
Community Champion
Community Champion

Hi @lazarus1907 ,

 

Check the attached file, it's a power query solution.

Capture.PNG



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@camargos88I found it very instructive, because I didn't know you could create recursive functions in Power BI, but it appears to "pull" the entire data as it created every new value, because I've tested it with a very long list (file is about 5 MB), which is something Excel would do within a second, but this takes forever and shows that it is reading a huge amount of data, probably because it re-reads the same data again and again. I have power BI refreshing this table right now, and it is been working on it for over 5 minutes, showing over 4 GB of data with a 5 MB source file, and I don't know how long it will continue doing that:

borra.JPG

 

 

 

 

 

 

 

 

 

I guess this answers the question: Power BI is NOT designed to do this kind of tasks.

Do you know whether Python or any other embedded language in Power BI would be more efficient for something like this?

Thanks anyway

@lazarus1907 ,

 

I've changed the code, instead of using a custom recursive function, now I'm using the List.Accumulate function.

Please, check if the performance is better now.

Capture.PNG



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@lazarus1907 ,

 

How large iss your real data ? 

I am gonna try to replicate it from my side and make some adjusments.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@camargos88Brilliant!! It works much better than what I hoped for. Thanks.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors