Forum Discussion
Evaluating Multiple fields to Calculate a Value
Hi JlmillerRedmond ,
Can you please confirm whether you have resolved issue. If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. This will be helpful for other community members who have similar problems to solve it faster.
If we don’t hear back, we’ll go ahead and close this thread.Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.
Thank you.
Hi JlmillerRedmond, I'm not sure if I understand the logic. I tried this one with 1 difference:
if [SalesLast3Mo] > 0 then [CurrentUserCount] else [PriorYrHigh]
Whole query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYQMgAcKGphBerE60khOypCFc0hQs6QxkGsPEQArMkSRdgEwTIDaC6TSCEiBJV2RjTaEYaqUbzFQDOIFkrDvUWEO4LphkLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Month = _t, PriorYrHigh = _t, SalesLast3Mo = _t, CurrentUserCount = _t, ReturnedHigh = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"SalesLast3Mo", type number}, {"CurrentUserCount", Int64.Type}, {"PriorYrHigh", Int64.Type}, {"ReturnedHigh", Int64.Type}}),
Ad_Result = Table.AddColumn(ChangedType, "Result", each if [SalesLast3Mo] > 0 then [CurrentUserCount] else [PriorYrHigh], Int64.Type)
in
Ad_Result
To maybe make it clearer, here is hypothetical for one customer with data in multiple months. The customer makes purchases some months, but user count doesn't change by the same amount or change at all. The RecordedHigh needs to go up when user count rises, but only if purchases are made.
Looking at month 7 above, user count goes up, but there are no purchases. For that month RecordedHigh should be the prior month recorded high. In some months, purchases may be made but user count drops. RecordedHigh remains the prior month amount.
Output:
Custom column code:
if [SalesLast3Mo] > 0 then List.Max({[PriorHigh], [CurrentUserCount]}) else [PriorHigh]Whole query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclHSUTIBYiMDIGEIYkCJWB2IpClMzBSmCknSDFnS0AJV0hwmCdZkjippAZM0BMuaQAiYrCVMDJskQocJNllDmCDCwUDZWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Month = _t, PriorHigh = _t, SalesLast3Mo = _t, CurrentUserCount = _t, RecordedHigh = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"PriorHigh", type number}, {"SalesLast3Mo", type number}, {"CurrentUserCount", type number}, {"RecordedHigh", type number}}), Ad_Result = Table.AddColumn(ChangedType, "Result", each if [SalesLast3Mo] > 0 then List.Max({[PriorHigh], [CurrentUserCount]}) else [PriorHigh], type number) in Ad_ResultThe calculated column text would work except that PriorHigh has to be calculated each month as well. It is the value in RecordedHigh for the preceding month.
Because RecordedHigh uses PriorHigh to determine which result to return, and because PriorHigh depends on RecordedHigh to return a result, a circular dependency occurs.
My formula for RecordedHigh essentially matches the proposed solution. But breaks because PriorHigh = RecordedHigh for Month-1
I think I understand what you need using a single customer as you show. (For multiple customers, you could do the same thing within a Table.Group function).
For the Source data, the only columns needed are Customer, Month, SalesLast3Mo, and CurrentUserCount.
The List.Generate function can then be used to calculate the PriorHigh and RecordedHigh columns, with no circular reference errors.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclHSUTIBYkMgNjJUitWBCJlCsZEBXMgMKmRoARcyB2IDkCpzuJAFSAVYzAQuZglThhACKzFBEzNEsjQWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Month = _t, SalesLast3Mo = _t, CurrentUserCount = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Customer", type text}, {"Month", Int64.Type}, {"SalesLast3Mo", Int64.Type}, {"CurrentUserCount", Int64.Type}}), #"Add Recorded High" = [a=List.Generate( ()=>[ph=null, rh=#"Changed Type"[CurrentUserCount]{0}, idx=0], each [idx] < Table.RowCount(#"Changed Type"), each [ph=[rh], rh=if #"Changed Type"[SalesLast3Mo]{[idx]+1}=0 then [rh] else List.Max({[rh],#"Changed Type"[CurrentUserCount]{[idx]+1}}), idx=[idx]+1], each {[ph],[rh]}), b=List.Transform(a, each _{0}), c=List.Transform(a, each _{1}), d=Table.FromColumns( Table.ToColumns(#"Changed Type") & {b} & {c}, type table[Customer=text, Month=Int64.Type, SalesLast3Mo=Int64.Type, CurrentUserCount=Int64.Type, PriorHigh=Int64.Type, RecordedHigh=Int64.Type] )][d], #"Reordered Columns" = Table.ReorderColumns(#"Add Recorded High", {"Customer", "Month", "PriorHigh", "SalesLast3Mo", "CurrentUserCount", "RecordedHigh"}) in #"Reordered Columns"
