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
sbone
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

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!!!
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...

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

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!!!
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...

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

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!!!
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...

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

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!!!
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...

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.
mrothschild
Continued Contributor
Continued Contributor

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
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.