Forum Discussion
Multiple conditions in aggregations
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},
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.
4 Replies
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.