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.