Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everyone!
I have a table here wich contains 4 different columns (A B C D)
I input the first value in column A (A1), and the other columns are calculated based on this value (B1 C1 D1).
For the next row, my value calculated in D1 becomes A2, and the operation repeats, calculating (B2 C2 D2).
Does anyone know how can i do this?
I tried to create a measure, but i can get it right.
Thank you so much!
Can you give a real example using real data? What you describe is for sure not possible since you have not provided a termination condition. As described, this process would not terminate and would if nothing else exhaust available memory. But, if you provide a little more insight into the nature of your problem, perhaps there is a way to make progress.
Hey Erik, thank you for answearing!
What happens is that i have an excel forecast table (with macro and vba) and i have to move this to power bi.
Basically what my excel table does is "predict", based on an animal inicial wheight and some energy calculations, a final animal weight per period.
My table is like this:
(Wheight0) (Period) (Inicial Period Weight) (Energy1) (Energy2) (Energy3) (Gain) (Final Wheight)
Basically it works like this:
So whats happens is:
(an example)
(Wheight0) (Period) (Inicial Period Weight) (Energy1) (Energy2) (Energy3) (Gain) (Final Wheight)
100 1 100 0,02 0,27 0,56 4 104
100 2 104 0,06 0,29 0,58 3,5 107,5
....
The termination condition is when the next period is a number lower that the number before.
Basically is this.
Tell me if you don't understand anything.
Thank you very much!
Camilla
Hi Camilla,
The formulas of Inicial Period Weight and Final Wheight can't nest each other. There could be loops. So we need to create them independently. Can you share the formula of Gain?
Why they can't nest each other?
[Inicial Period Weight] could be if ([period] = 1, 100, [Final Wheight]).
[Final Wheight] could be [Inicial Period Weight] + [gain].
When period equals 2, [Inicial Period Weight] = [Final Wheight] while [Final Wheight] = [Inicial Period Weight] + [gain].
Best Regards!
Dale
Hey Dale, thanks for your answer!
The gain formula is:
=0,42*(Inicial Period Wheight)
And the final wheight formula is:
=Gain+Inicial Period Wheight
I was trying to use de DAX EARLIER(), but it didn't work
Thanks a lot!!
Best,
Camilla
Hi @Camillanaep,
I am not sure you can do this in DAX, but you certainly can in Power Query using the M language. I would regard this as a relatively advanced usage of these tools as you will have to enter M formulas rather than working through the GUI and be comfortable with recursive functions (this is how you implement iterations in functional languages such as M), but anyway, here it is.
First, bring up the Power Query window. Click New Source - Blank Query. Click Advanced Editor and copy paste the following into the dialog:
Let
GenerateWeightTable = (StartWeight) => let GWeight = (WeightList, Period, Weight) => if (Period <= 15) then @GWeight(WeightList & {{StartWeight,Period,Weight,1,2,3,0.045*Weight, Weight + 0.045*Weight}},
Period + 1,
Weight + 0.045*Weight) else WeightList in Table.FromRows(GWeight({},1,StartWeight), {"(Weight0)","(Period)","(Initial Period Weight)",
"(Energy1)","(Energy2)","(Energy3)","(Gain)",
"(Final Weight)"}) in GenerateWeightTable
I haven't quite figured out what the formulas are supposed to be but you can edit the
{StartWeight,Period,Weight,1,2,3,0.045*Weight, Weight + 0.045*Weight}
portion as you see fit and replace 1, 2, 3 with whatever formulas you have for these energy coefficients and the same for the gain etc. Also, I have set this so that the recursion stops once Period hits 15. If you have a different termination criteria you can edit the if statement accordingly. Just be careful so that it actually does terminate...
You can now execute the function in Power Query by entering a starting weight (100 in your example) and click Invoke. You will get a nice little table with 15 rows as a result. Hope this helps.
Thanks for your answer Erik!
I understand your formula, it helped me a lot.
The problem that i have now is to put this code into my power query window, because there's already a code there, showing all the changes that i've done:
let
Source = Excel.Workbook(File.Contents("C:\Users\c313355\Documents"), null, true),
#"Pen Inputs_Sheet" = Source{[Item="Pen Inputs",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Pen Inputs_Sheet",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type text}, {"Column7", type any}, {"Column8", type text}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type text}, {"Column19", type text}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Promoted Headers1" = Table.PromoteHeaders(#"Promoted Headers", [PromoteAllScalars=true]),
#"Filtered Rows" = Table.SelectRows(#"Promoted Headers1", each ([#"Pen"] <> null))
in
#"Renamed Columns8"
This columns that you've created for me (period, wheight, inicial period wheight) already exists in the query, so i just need to reference them in the formulas.
Do you know how can i input this formulas into this power query window above? i tried to add another "let" and then put your formulas, put it wasn't right.
Thanks!
Camilla
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |