Multiple conditions in aggregations

[Anonymous]

Hi

the code below written by ronrsnfld 

See original post here:

Solved: Re: Average records before and after given date - Microsoft Fabric Community

        {"Repair Date", each [Repair Date]{0}, type date},

        {"avg 3 repairs cost before",  (t)=> 
            List.Average(
                List.LastN(
                    Table.SelectRows(t, each [Failure Date] < [Repair Date])[Repair Cost],
                3)
            ), type number},

The code accommodates for one condition:

each [Failure Date] < [Repair Date])[Repair Cost]

what if AND / OR conditions are required? what would be the revised code?

I've tried the following but it didn't works!

        {"Repair Date", each [Repair Date]{0}, type date}, {"Insured Date", each [Insured Date]{0}, type date}

        {"avg 3 repairs cost before",  (t)=> 
            List.Average(
                List.LastN(
                    Table.SelectRows(t, each [Failure Date] < [Repair Date])[Repair Cost] and [Failure Date] < [Insured Date])[Repair Cost],
                3)
            ), type number},

[Anonymous]

Oh by the way y'all, I've been getting the first column values by using each Table.FirstN(Table, 1)[Insured Date]{0}. Also Table.FirstValue([[Column]]) is a good choice as well.

--Nate 

It's optimized somehow--test it out!

[123abc · Accepted solution]

In Power Query M language, you can use the "and" or "or" conditions to combine multiple conditions. However, it looks like you have a small syntax error in your code. You should use the "and" operator within the Table.SelectRows function. Here's the revised code with both conditions:

{"Repair Date", each [Repair Date]{0}, type date},
{"Insured Date", each [Insured Date]{0}, type date},
{"avg 3 repairs cost before", (t)=>
List.Average(
List.LastN(
Table.SelectRows(t, each [Failure Date] < [Repair Date] and [Failure Date] < [Insured Date])[Repair Cost],
3
)
), type number
}

In the revised code, I've used the "and" operator to ensure that both conditions [Failure Date] < [Repair Date] and [Failure Date] < [Insured Date] are met when filtering the rows using Table.SelectRows. This should calculate the average of "Repair Cost" for rows that satisfy both conditions.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Anonymous]

123abc

Thanks a lot.

I had done multiple code corrections but seems I missed parenthesizes 🙂

It is working now. thanks for your prompt support.

[123abc]

You are always wellcome.