Forum Discussion

dadorsey's avatar
dadorsey
Frequent Visitor
7 years ago
Solved

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...
Need Help
  • d_gosbell's avatar
    d_gosbell
    7 years ago

    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

d_gosbell's avatar
d_gosbell
Icon for Super User rankSuper User
7 years ago

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

dadorsey's avatar
dadorsey
Frequent Visitor
7 years ago

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.