Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Solved! Go to 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.
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"
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.
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.
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).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |