Forum Discussion

cemapple's avatar
cemapple
Frequent Visitor
6 years ago

Filter using a range based on aggregate values

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.

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      cemapple
      Frequent Visitor
      6 years ago

      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_CDCount of MemberIDMember_Range
      AZ910Less Than 100
      CA49Less Than 100
      FL8000Less Than 100
      KY5500Less Than 100
      TN15Less Than 100
      TX1567Less Than 100
      WI345Less 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.