Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JJ51
Frequent Visitor

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!

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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 #] )

View solution in original post

5 REPLIES 5
tamerj1
Super User
Super User

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 #] )

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 ?

@MaherKbi 
Would you please place a screenshot?

JJ51
Frequent Visitor

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 #)

Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

Hi @JJ51 .  Power BI has the ability to create Bins.  Here are couple of examples:

 

Creating Groups and Histogram Bins in Power BI - YouTube

Create Buckets or Groups with Power Query in Power BI - YouTube

 

In you situation, I would be attempted to add a Calculated Column to the main table with the "Count of Changes".  This might be required to create the Bins for the grouping.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.