I'm struggling with an expression to average a list of values based on a number of conditions. I've gotten the expression part of the way there using COUNT(), but need to AVERAGE(). Here's the expression thus far:
Count(Select(StephensonCourt_Tbl[CourtID],AND([CourtDate]>=Today()-7,TEXT(ANY([Related StephensonVPRAIR_Tbls][ChargeCategory]))=โNon Violent Misdemeanorโ,TEXT(ANY([Related StephensonBondHearing_Tbls][ReleaseRecognizance]))=โNoโ)))
I need to go to the StephensonCourt_Tbl, CourtID, the CourtDate and only check records for the last 7 days, and go the StephensonVPRAIR_Tbl, ChargeCategory count those that are = "Non Violent Misdemeanorโ and go to the StephensonBondHearing_Tbls, ReleaseRecognizance = "No" and go to the StephensonBondHearing_Tbls, BondAmount column and average the values in this column that match, BondAmount.
I hope I'm making sense, Any help or suggestions you might have are much appreciated.
Thank you!
Solved! Go to Solution.
Your expression is far too complex and imbricated.
I suggest you bring back some informations step by step from the last table to the main one.
For example, you could make:
- column "part1" with expression:
[Related StephensonVPRAIR_Tbls][ChargeCategory]
- column "part2" with expression:
[Related StephensonBondHearing_Tbls][ReleaseRecognizance]
and so on. That will help you into building your last expression.
Also, this part:
SELECT(StephensonCourt_Tbl[CourtID]
is strictly equivalent to:
FILTER("StephensonCourt_Tbl"
(if [courtID] is the key-column of the table StephensonCourt)
Also:
AVERAGE() requires a list of number-type values.
SELECT(StephensonCourt_Tbl[CourtID] will likely be a list of ID from the table StephensonCourt_Tbl
Once you break you main expression apart, can you share us a screenshot of the table columns from the editor ?
Your expression is far too complex and imbricated.
I suggest you bring back some informations step by step from the last table to the main one.
For example, you could make:
- column "part1" with expression:
[Related StephensonVPRAIR_Tbls][ChargeCategory]
- column "part2" with expression:
[Related StephensonBondHearing_Tbls][ReleaseRecognizance]
and so on. That will help you into building your last expression.
Also, this part:
SELECT(StephensonCourt_Tbl[CourtID]
is strictly equivalent to:
FILTER("StephensonCourt_Tbl"
(if [courtID] is the key-column of the table StephensonCourt)
Also:
AVERAGE() requires a list of number-type values.
SELECT(StephensonCourt_Tbl[CourtID] will likely be a list of ID from the table StephensonCourt_Tbl
Once you break you main expression apart, can you share us a screenshot of the table columns from the editor ?
Thanks for your help with this. You got me on the right track after thinking it through.
User | Count |
---|---|
26 | |
25 | |
25 | |
19 | |
17 |