cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Pass Parameter Values to Calculated Table Created in M

All,

 

I'm using Andrew Todd's fantastic M query (below) to create a mortgage table on-the-fly in Power BI. I need to be able to let a user pass the key variables (p, i, and n) from a Power BI report to the M query to recalculate the table based on the user's variables. Help! I can't find a way to do this and it's driving me crazy! Is this even possible? 

Thanks in advance for any help you can give!

Here's the M Query:

let

P = 250000,
i=0.05,
n = 360,

Payment = P*((i/12)*Number.Power(1+(i/12),n))/(Number.Power(1+(i/12),n)-1),

Payments =	Table.FromList(
List.Generate(
()=>[Counter=0, Balance=P, MonthlyInterest=0, MonthlyPrincipal=0, MonthlyPayment=0],
each [Counter]<n,
each [ Counter=[Counter]+1,

Balance=P*(Number.Power(1+(i/12),n)-Number.Power(1+(i/12),[Counter]+1))/(Number.Power(1+(i/12),n)-1),

MonthlyInterest = (i/12)*Balance,

MonthlyPrincipal = Payment-MonthlyInterest,

MonthlyPayment = Payment],

each [[Counter], [Balance], [MonthlyInterest], [MonthlyPrincipal], [MonthlyPayment]])
, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(Payments, "Column1", {"Counter", "Balance", "MonthlyInterest", "MonthlyPrincipal", "MonthlyPayment"}, {"Payment Number", "Balance", "Monthly Interest", "Monthly Principal", "Monthly Payment"})

in
#"Expanded Column1"

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Yes, you could create 3 measures for P, i and n. Then, you could create a new table using the New Table under the Modeling tab of the ribbon. You will need to use GENERATESERIES.

 

I may have time to give you the exact answer later on tonight if I do not have too many beverages. I've already had several so no guarantees. But essentially, you would create a table with GENERATESERIES to create your initial table. The rest should be just adding columns to that table using the same basic math as in the Power Query code. You could refer to your measures in your code.

 

GENERATESERIES

https://msdn.microsoft.com/en-us/library/mt842624.aspx

 

Note that I also ponder whether or not a static table is really the most suitable vehicle for whatever it is that you are solving or whether a dynamic table would be better suited to the ultimate goal of what you are trying to achieve. But, I do not understand your ultimate goal, I assume it involves some version of taking over the world, but specifics would help. Unless it compromises your ultimate goal of world domination and in that case, I understand if you want to be cagey about things.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

7 REPLIES 7
Highlighted
Super User IV
Super User IV

So, if you use Manage Parameters to create parameters named P, i and n then you can do this:

 

let

P = P,
i=i,
n = n,

Payment = P*((i/12)*Number.Power(1+(i/12),n))/(Number.Power(1+(i/12),n)-1),

Payments =	Table.FromList(
List.Generate(
()=>[Counter=0, Balance=P, MonthlyInterest=0, MonthlyPrincipal=0, MonthlyPayment=0],
each [Counter]<n,
each [ Counter=[Counter]+1,

Balance=P*(Number.Power(1+(i/12),n)-Number.Power(1+(i/12),[Counter]+1))/(Number.Power(1+(i/12),n)-1),

MonthlyInterest = (i/12)*Balance,

MonthlyPrincipal = Payment-MonthlyInterest,

MonthlyPayment = Payment],

each [[Counter], [Balance], [MonthlyInterest], [MonthlyPrincipal], [MonthlyPayment]])
, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(Payments, "Column1", {"Counter", "Balance", "MonthlyInterest", "MonthlyPrincipal", "MonthlyPayment"}, {"Payment Number", "Balance", "Monthly Interest", "Monthly Principal", "Monthly Payment"})

in
#"Expanded Column1"

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted

Ok thank you Greg. This may be a dumb question, how can my users then change those parameters from a report?
Highlighted

Hmmm, yeah that's a good question. How will you be sharing this report with others? To do it this way, you would need to share the PBIX file and then users could change the parameters before they refresh the query. Depending on how you want to share this, that is going to answer that question. This might have to be moved to DAX or potentially something else. 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted

Agreed. Is there a way to recreate this calculated table in DAX?
Highlighted

Yes, you could create 3 measures for P, i and n. Then, you could create a new table using the New Table under the Modeling tab of the ribbon. You will need to use GENERATESERIES.

 

I may have time to give you the exact answer later on tonight if I do not have too many beverages. I've already had several so no guarantees. But essentially, you would create a table with GENERATESERIES to create your initial table. The rest should be just adding columns to that table using the same basic math as in the Power Query code. You could refer to your measures in your code.

 

GENERATESERIES

https://msdn.microsoft.com/en-us/library/mt842624.aspx

 

Note that I also ponder whether or not a static table is really the most suitable vehicle for whatever it is that you are solving or whether a dynamic table would be better suited to the ultimate goal of what you are trying to achieve. But, I do not understand your ultimate goal, I assume it involves some version of taking over the world, but specifics would help. Unless it compromises your ultimate goal of world domination and in that case, I understand if you want to be cagey about things.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

Highlighted

That’s hilarious. I’m exactly 1.5 beverages past being able to dig back in to this so brother ai understand.

I run a loan trading desk and when we get pools of loans in that I want to resell we create a Power BI report that’s basivally an online dashboard to show loan pool characteristics. My goal is to create a mechanism that will allow a client to take our report (viz), and calculate yields using different parameters, which would require a calculated table to be built on-the-fly inside of Power BI, since we can’t go back and change the source data in Excel. Truth is, though, that my wife is going to kill me if I spend any more time on this today, so I’m going to drink another beverage, watch Villanova dispatch Kansas, and wake up to fight with this another day. In all seriousness, I really appreciate your help here. I’ve sort of become an accidental data guy and I feel out of my element.
Highlighted

Realize I'm way late to this thread, but wondering if a DAX solution for calculated tables was every developed or work-around solved? I'm trying to accomplish something similar to the original question, but don't know M (and barely know DAX, have only been working with it for a few weeks).

 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors