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.
