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

## Determine min number by category while excluding records from consideration

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
1 ACCEPTED SOLUTION
Super User

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

2 REPLIES 2
Super User

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Frequent Visitor

Thank you @TheoC.  That worked great!!!

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.