Generate a level of achievement from a table

[OneBItterGuy]

Hello

I have a table with data in it.  The table has columns for User ID, Score, Category, SubCategory and measures for Type (which groups the category into 5 different groups) and Max Score per Category. My end goal is be able to have a Page with a slicer To select the user and then display stats about that user, such as their current achievement level.  I want to create a measure that will calculate the acievement leve, and return an integer based on what has been achieved. No Rank, would return a 0, Level 1 would return a 1, Level 2 a 2 and so on.  I have a measure written that calculates the lowest achievements. 

Rank =
VAR GreaterThan20 = CALCULATE(COUNT(Table[Score]),FILTER(Table, Table[Score] >= 21))
VAR CategoriesMax = CALCULATE(MAX(Table[Score]),ALLEXCEPT(Table, Table[Category], Table[UserID]))
VAR Level1 = IF(GreaterThan20 >= 5 && CategoriesMax >= 4, 1, 0)
VAR Level2 = IF(GreaterThan20 >= 10 && CategoriesMax >= 7, 2, 1)

RETURN IF (Level2 > Level1, Level2, Level1) 

The Next Level Requries me to ensure one of the GreaterThan20 Scores comes from a Type Determined from the Measure Type.  There are 5 different types; 0, 1, 2, 3, 4. I just need to ensure 1 of them is not from type 0. How can I encorporate this into my formula?

[Greg_Deckler]

@OneBItterGuy Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[OneBItterGuy]

@Greg_Deckler here is a sample of the data

Category	Subcategory	UserID	Score	Type
C1	C1A	1	43	CIDER
28	28B	1	30	SOUR
6	6C	1	46	LAGER
1	1D	1	28	ALE
10	10A	1	34	ALE
25	25B	1	34	ALE
33	33B	1	40	ALE
8	8A	2	31	LAGER
18	18A	2	34	ALE

There are 40 categories, in total 146 subcategories.  There are multiple users, with unique userIDs. There are scores for each of the 146 subcategories, for each user. And there are 5 string values available for the type field.

My end goal is to see what achievment each user has. This is based off of a list of requirements with the above data. For instance, level 1 requires 5 scores to be 21 or greater, and be from 4 unique Categories. Level 2 builds on Level 1requiring 10 scores of 21 or greater and be in 7 unique categories.  I am running into an issue when I try to incoporate the Type column into my calculations. When I am working on Level 3,  besides the above requirements, scores now have to have at least one belonging to the type "LAGER", "CIDER", "MEAD", or "SOUR". Or to put more simply, must not come from the Type "ALE"  This is where I am struggling. I am looking in my measure to create a VAR that will count the quantity of scores above X that come from the Types above.

[Greg_Deckler · Accepted solution]

@OneBItterGuy Try this. PBIX is attached below signature.

Rank = 
    VAR __Table = FILTER('Table', 'Table'[Score] >= 21)
    VAR __GreaterThan20 = COUNTROWS(__Table) + 0
    VAR __NumCategories = COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table, "__Category", [Category]))) + 0
    VAR __NotAle = COUNTROWS(FILTER(__Table, [Type] <> "ALE")) + 0
    VAR __Level1 = IF(__GreaterThan20 > 4 && __NumCategories > 3, 1, 0)
    VAR __Level2 = IF(__GreaterThan20 > 9 && __NumCategories > 6, 2, 1)
    VAR __Level3 = IF(__Level2 = 2 && __NotAle > 0, 3, __Level2)
    VAR __Result = MAX(MAX( __Level1, __Level2), __Level3)
RETURN
    __Result

[OneBItterGuy]

Thank you for your help!