Forum Discussion
Filter using a range based on aggregate values
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
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.
