Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |