cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculate a minimum value across multiple rows based on multiple fields

Hi,

I'm new to Power BI and struggling to figure out how to calculate a column.

My data has rows of unique transactions representing different problem transactions identified on an account. Each row shows the amount of the individual problem transaction and the year/month in which the problem transaction occurred. It also contains a running monthly total for that problem type and account. What I'm trying to do is calculate the last column shown in the sample below--identifying the earliest Year and Month for which the Monthly Running Total equals or exceeds 1,000 for a given problem type and account. If the running total for that problem type and account never equals or exceeds 1,000 I need no value returned for that column.

Based on my reading it seems like I should be able to use the CALCULATE function to find the minimum Year and Month in which the running total first hit or exceeded 1,000 for a given problem type and account, but so far my attempts to do so have all failed.

Any suggestions would be greatly appreciated.

Thanks!

 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 201903 A 1 002 201903 600 1100 201903 A 2 003 201901 800 800 B 1 004 201901 500 1000 201901 B 1 005 201901 500 1000 201901 B 2 006 201902 1200 1200 201902

1 ACCEPTED SOLUTION
Super User

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

7 REPLIES 7
Super User

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

Frequent Visitor

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.

Super User

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```
Frequent Visitor

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
Super User

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```

Frequent Visitor

Yes, that's working. Thanks again for the help and for explaining so clearly how you got there.

Frequent Visitor

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors