Determine min number by category while excluding records from consideration

[tlipkin]

I need to add a column to use for further analysis that gives me the minimum ItemId for each Category. In addition, though, I need to not consider ItemId records that have a Status of “Withdrawn” when determining which is the minimum ItemId.  I have been successful the following two ways in determining the minimum ItemId by Category but I don’t know how to add the filter to exclude the “Withdrawn” Status when performing this calculation. 

CALCULATE(MIN(table1[ItemId]),FILTER(table1,table1[Category]=EARLIER(table1[Category])))

CALCULATE (MIN (table1[ItemId]),ALLEXCEPT (table1, table1[Category])

Here is a sample dataset and what my expected result is.

Category	ItemId	Status	Min Item # Not Withdrawn
1005189150	2106967	Withdrawn	2106968
1005189150	2106968	Completed	2106968
1005189150	2108134	Withdrawn	2106968
1005189150	2108139	Withdrawn	2106968
1005189150	2108165	Withdrawn	2106968
1005189150	2108204	Withdrawn	2106968
1005189150	2108206	Completed	2106968
1005268528	2118244	Withdrawn	2119115
1005268528	2119115	Active	2119115
1005268528	2126390	Withdrawn	2119115
1005268528	2129706	Active	2119115
1005268528	2131912	Active	2119115
1005268528	2131922	Active	2119115

[TheoC · Accepted solution]

Hi @tlipkin 

You were quite close with your approach.  A slight adjustment to the below using a Calculated Column will get you what you're after:

_col = 

VAR _1 = 
    CALCULATE ( 
        MIN ( _Table[ItemId] ) ,
        _Table[Status] <> "Withdrawn" ,
        ALLEXCEPT ( _Table , _Table[Category] ) )

RETURN

_1

Output is as per below screenshot:

Hope this helps 🙂

Theo

[tlipkin]

Thank you @TheoC.  That worked great!!!