Forum Discussion

JJ51's avatar
JJ51
Frequent Visitor
4 years ago
Solved

Create Categories Based On Count From a Measure

Hey all!

 

I'd like to preface by saying that I am mostly new to Power BI, not a programmer, and I am self-taught, so if this question seems obvious or is a standard practice, please forgive my ignorance.

 

I am trying to determine the best way to grab a count of a field and then break that count into categories.  My goal is to take a count of issues (they have a unique key) in a changelog, filtered by the same unique key in the main table. Depending on the result, I want catergorize it into buckets i.e. 1-3, 4-6, 6-9, etc.   I want to use these as a legend for a pie chart, and then get a count of issues that fit into each category. 

 

This is a rudimentary example:

 

Main Table - high level list of issues and associated fields

Issue #DescriptionCreation DateStatus
1Bug1/1/2022Closed
2Bug1/2/2022Closed
3Change Request1/3/2022Open
4Bug1/4/2022Closed
5Enhancement1/5/2022Closed
6Bug1/6/2022Open
7Bug1/7/2022Closed
8Change Request1/8/2022Closed
9Bug1/9/2022Open
10Enhancement1/10/2022Closed

 

Changelog:

 

Issue #Change TypeChange Date
1Status Change1/2/2022
1Comment1/3/2022
1Status Change1/4/2022
1Comment1/6/2022
2Status Change1/5/2022
2Comment1/6/2022
2Status Change1/7/2022
2Comment1/8/2022
2Status Change1/9/2022
3Comment1/10/2022
3Status Change1/2/2022
4Comment1/3/2022
4Status Change1/4/2022
4Comment1/6/2022
5Status Change1/5/2022
6Comment1/6/2022
6Status Change1/7/2022
6Comment1/8/2022
6Status Change1/9/2022
6Comment1/10/2022
6Status Change1/2/2022
7Comment1/3/2022
7Status Change1/4/2022
7Comment1/6/2022
7Status Change1/5/2022
8Comment1/6/2022
9Status Change1/7/2022
9Comment1/8/2022
9Status Change1/9/2022
9Comment1/4/2022
10Status Change1/6/2022
10Comment1/5/2022

 

Count Result:

Issue #Changes
14
25
32
43
51
66
74
81
94
102

 

 

I am able to get the count data easily without a measure, but I cannot bucket it into groups.  Any quick thoughts on how to accomplish this?  I am assuming this will require using variables, but I haven't the slightest idea where to start. 

 

Thank you!

Categories
DAX
legend
measure
Pie Chart
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi JJ51
    Here is a sample file with the solution https://www.dropbox.com/t/X2AzilJNQyYDLliO

    You nedd to have a calculated column

    Buckets = 
VAR NumberOfChanges =
    COUNTROWS (
        CALCULATETABLE ( 
            Changelog,
            ALLEXCEPT ( Changelog, Changelog[Issue #] )
        )
    )
VAR Result =
    SWITCH ( 
        TRUE(),
        NumberOfChanges IN { 1, 2, 3 }, "1-3",
        NumberOfChanges IN { 4, 5, 6 }, "4-6",
        NumberOfChanges IN { 7, 8, 9 }, "7-9",
        NumberOfChanges IN { 10, 11, 12 }, "10-12",
        NumberOfChanges IN { 13, 14, 15 }, "13-15",
        ">15"
    )
RETURN
    Result

    The the measure would be

    Count = DISTINCTCOUNT ( Changelog[Issue #] )

5 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi JJ51
    Here is a sample file with the solution https://www.dropbox.com/t/X2AzilJNQyYDLliO

    You nedd to have a calculated column

    Buckets = 
VAR NumberOfChanges =
    COUNTROWS (
        CALCULATETABLE ( 
            Changelog,
            ALLEXCEPT ( Changelog, Changelog[Issue #] )
        )
    )
VAR Result =
    SWITCH ( 
        TRUE(),
        NumberOfChanges IN { 1, 2, 3 }, "1-3",
        NumberOfChanges IN { 4, 5, 6 }, "4-6",
        NumberOfChanges IN { 7, 8, 9 }, "7-9",
        NumberOfChanges IN { 10, 11, 12 }, "10-12",
        NumberOfChanges IN { 13, 14, 15 }, "13-15",
        ">15"
    )
RETURN
    Result

    The the measure would be

    Count = DISTINCTCOUNT ( Changelog[Issue #] )
    • JJ51's avatar
      JJ51
      Frequent Visitor
      4 years ago

      Thank you! The calculated column did exactly what I needed.  However, I substituted the entire NumberOfChanges variable for the measure.   The measure was also changed to just a count of Issue# in the Changelog, and due to a relationship between the the two tables (Issue #)

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi, 
      i'm trying to create a dyanmic category for my measure result which is in percent.

      but it's not working, it display juste 1 same value for all

      Percent Category =
      VAR Per = [% measure]
      VAR Result =
          SWITCH (
              TRUE(),
              Per <= 1.10, "0-110%",
              (Per >= 1.11 && NumberOfChanges < 125), "110-125%",
              (Per >= 1.26 && NumberOfChanges < 150), "125-150%",
              "150%+"
          )
      RETURN
          Result
       
       
      Have you any idea ?
      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        Anonymous 
        Would you please place a screenshot?