Forum Discussion
Calculate a minimum value across multiple rows based on multiple fields
You should be able to use a calculation like the following:
Measure = var _table = CALCULATETABLE(
Table1,
ALL(Table1),
VALUES(Table1[Problem Type]),
VALUES(Table1[Account]),
Table1[Monthly Running Total] >= 1000) RETURN MINX(_table, Table1[Transaction Year and Month])
Firstly I'm creating the _table varialbe using the ALL function to remove all filters from the table, then I add back just the Account and Problem Type filters as well as a filter for where the running total is >= 1000Then I using MINX to find the minimum month from this variable
You should be able to use a calculation like the following:
Measure = var _table = CALCULATETABLE(
Table1,
ALL(Table1),
VALUES(Table1[Problem Type]),
VALUES(Table1[Account]),
Table1[Monthly Running Total] >= 1000) RETURN MINX(_table, Table1[Transaction Year and Month])
Firstly I'm creating the _table varialbe using the ALL function to remove all filters from the table, then I add back just the Account and Problem Type filters as well as a filter for where the running total is >= 1000
Then I using MINX to find the minimum month from this variable
Brilliant! Thanks so much! That works.
If I wanted to use that new calculated measure column in a slicer is there a way to do that? It seems like that may be not allowed with a calculated measure as when I try to pull it into one Power BI won't accept it. Am I just overlooking something simple there?
Thanks again, really appreciate the help.
dadorsey wrote:If I wanted to use that new calculated measure column in a slicer is there a way to do that? It seems like that may be not allowed with a calculated measure as when I try to pull it into one Power BI won't accept it. Am I just overlooking something simple there?
No, if you want to use something in a slicer you would need to use a column not a measure. If you wanted to do the same thing in a calculate column I would change the expression slightly. It's usually better to avoid using CALCULATE or CALCULATETABLE in column expressions if you can.
I would do something like the following for a column calc.
Column = var _currentAccount = Table1[Account] var _currentProblemType = Table1[Problem Type] var _table = FILTER( ALL(Table1),Table1[Problem Type] = _currentProblemType && Table1[Account] = _currentAccount && Table1[Monthly Running Total] >= 1000) var _result = MINX(_table, Table1[Transaction Year and Month]) return _result
Thanks again. That column calculation works as expected too. Between those two examples I can see a lot of what I was doing wrong when I was trying to get the calculation on my own.
This column calculation is working exactly as expected but I ran across an unexpected wrinkle in my data as I was implementing it. Occasionally the data includes negative amounts that drive the running total back down below $1,000. In those instances the column is reporting the earliest year and month in which it hit the 1K threshold but I'd like it to return no value in this new column if the most recent year and month for that problem type and account is no longer 1K or over. Similarly if it hits the 1K threshold, later dips down below it, then later rises above it (like problem type C below) I'd like it to give me the earliest year and month in which it most recently hit the 1K threshold.
This seems a lot more complicated than the problem I thought I was trying to solve. Any thoughts on how I might be able to incorporate that more complicated check?
Thanks!
Damon
Problem Type
Account
Transaction ID
Transaction Year and Month
Amount
Monthly Running Total
Min Year and Month Running Total >=1K
A
1
001
201901
500
500
A
1
002
201902
600
1100
A
1
003
201903
-400
700
B
2
004
201901
1000
1000
B
2
005
201902
-100
900
C
3
006
201901
1100
1100
201903
C
3
007
201902
-200
900
201903
C
3
007
201903
200
1100
201903
I think the following alteration to the calculated column expression should meet those new requirements. I've renamed some of the variables to make it easier to see what values they store. Basically I added an extra step to get the max date where the value was below 1000 then I only get the min date where it was after that date and greater than 1000. So the value should be able to go above and below 1000 multiple times and we will still only get the last date where it first went above 1000
Column = var _currentAccount = Table1[Account] var _currentProblemType = Table1[Problem Type] var _datesBelow1000 = FILTER( ALL(Table1),Table1[Problem Type] = _currentProblemType && Table1[Account] = _currentAccount && Table1[Monthly Running Total] < 1000) var _maxDateBelow1000 = MAXX(_datesBelow1000, Table1[Transaction Year and Month]) var _datesAbove1000 = FILTER( ALL(Table1), Table1[Problem Type] = _currentProblemType && Table1[Account] = _currentAccount && Table1[Monthly Running Total] >= 1000 && Table1[Transaction Year and Month] > _maxDateBelow1000) var _lastMinDateAbove1000 = MINX(_datesAbove1000, Table1[Transaction Year and Month]) return _lastMinDateAbove1000