Filter using a range based on aggregate values

[cemapple]

I'm new to DAX and Power BI.  I'm trying to create a grouping or range based on the sum of distinct count of values.  In Excel I would create something like "If (Count(Member ID) < 100, "1 - 99", "100 +").  How can you replicate the same affect and use the grouping/range of values as a legend in a visual? When I attempt the same formula in Power BI, all values result in "1 - 99".   I was attempting to create a map and use the grouping/range to color code the states by the count of member ID.

[Anonymous]

Hello-

   This should work very similar in DAX but with distinctcount.

Fomula = if(calculate(distinctcount('Member ID Table' [Member  ID]) <100, "Less than 100","Other)) or as you would like for however many groups you want.

Jared

[cemapple]

Jared,

Thanks for responding, but apparently I'm doing something incorrect.  My dataset has 2 columns, MemberID & State (the member ID's are dummy format A1001, A1002, etc).   Both are text data types. 

My calculated column:  IF(CALCULATE(DISTINCTCOUNT(State_Members[MemberID])<100),"Less Than 100", IF(CALCULATE(DISTINCTCOUNT(State_Members[MemberID])< 1000), "100 - 1000", "1001 +"))

When I create a table visualization, it returns "Less than 100" for all row values. 

Result:

State_CD	Count of MemberID	Member_Range
AZ	910	Less Than 100
CA	49	Less Than 100
FL	8000	Less Than 100
KY	5500	Less Than 100
TN	15	Less Than 100
TX	1567	Less Than 100
WI	345	Less Than 100

I can't figure out how to attach the .pbix file or my organization has blocked it.  It seems I can't copy or paste anything either into the body of the text.