Recursive measure challange in DAX. From Excel to Power BI

[Yellow_hill]

Hi all,

I have a challange to move a report from Excel to Power BI due to the following measure.

For cell C3 the formula is as follows "=IF(OR(COUNTIF(B3:B8;"<"&C2)=6;COUNTIF(B3:B8;">"&C2)=6);AVERAGE(B3:B8);C2)"

For the next cells in "C" column it's the same formula. The only exception is cell "C2" where the formula is just "=AVERAGE(B2:B7)".

So my main challange is with formula "=IF(OR(COUNTIF(B3:B8;"<"&C2)=6;COUNTIF(B3:B8;">"&C2)=6);AVERAGE(B3:B8);C2)" because in order to calculate value for "C3" I need to calculate what was calculated in "C2" thus I think it's a Recursive problem which Power BI is not especially good at. But perhaps there is some workaround to do this. I appreaciate any advises.

[Yellow_hill]

Hi @Anonymous 

Thank you for your effort and prompt help. I've studied your solution but it's not quite right. But maybe it's my fault because perhaps I was not clear enough.

As I mentioned formula for cell C3 is as follows "=IF(OR(COUNTIF(B3:B8;"<"&C2)=6;COUNTIF(B3:B8;">"&C2)=6);AVERAGE(B3:B8);C2)".

Later on formula for cell C4 is =IF(OR(COUNTIF(B4:B9;"<"&F3)=6;COUNTIF(B4:B9;">"&F3)=6);AVERAGE(B4:B9);F3)

Later formula for cell C5 is =IF(OR(COUNTIF(B5:B10;"<"&F4)=6;COUNTIF(B5:B10;">"&F4)=6);AVERAGE(B5:B10);F4) and so on. So the formula from C3 is dragged until the last cell in "C" column.

In you solution you assumed that "=IF(OR(COUNTIF(B3:B8;"<"&C2)=6;COUNTIF(B3:B8;">"&C2)=6);AVERAGE(B3:B8);C2)" applies for all cells from C3 to C13 and this is not quite it. 

It would be great if you could verify if this can be done with these new requirments?

Rgrds

[Anonymous · Accepted solution]

Hi @Yellow_hill 

Powerbi isn't good at dealing with recursion,In Power Query you can do it check the articles below with some examples that you can adjust to your needs:

https://www.thebiccountant.com/2017/09/26/recursion-m-beginners/

https://www.thepoweruser.com/2019/07/01/recursive-functions-in-power-bi-power-query/

https://xcelanz.com/recursive-functions-in-power-query/

You can process the data in Excel and then import it into Powerrbi, which will help you save a lot of time.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Yellow_hill 

I've created the following table in response to your question.

You can implement your needs in Power Query

Below is the M code for the implementation，You can also view it in the pbix file I attached

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Nce7DQAxCATRXoidGDCfWpD7b+PWJ20yejMjW5bsF7lrRAFVngFmPAfceQc4hxdABC+BTF4BVbwGuv+7Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [index = _t, Value = _t, #"Center line" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"index", Int64.Type}, {"Value", Int64.Type}, {"Center line", type text}}),
    

   

    AverageFirstSix = List.Average(List.FirstN(#"Changed Type"[Value], 6)),
    AverageSecondToSeventh = List.Average(List.Range(#"Changed Type"[Value], 1, 6)),

    // Get the values from the second row to the seventh row of the value column
    ValuesSecondToSeventh = List.Range(#"Changed Type"[Value], 1, 6),

    // Check if AverageFirstSix is greater or less than all the values extracted above
    AllLess = List.AllTrue(List.Transform(ValuesSecondToSeventh, each _ < AverageFirstSix)),
    AllGreater = List.AllTrue(List.Transform(ValuesSecondToSeventh, each _ > AverageFirstSix)),
    AllGreaterOrLess = AllLess or AllGreater,
    // Add custom columns
    CustomColumn = Table.AddColumn(#"Changed Type", "Center Line", each if [index] = 1 then AverageFirstSix
        else if AllGreaterOrLess
        then AverageFirstSix
        else AverageSecondToSeventh
        ),
    FinalTable = Table.SelectColumns(CustomColumn, {"index", "Value", "Center Line"})

in
    FinalTable

This is the result you want

^{Best Regards,}

^Jayleny

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}