Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 # | Description | Creation Date | Status |
1 | Bug | 1/1/2022 | Closed |
2 | Bug | 1/2/2022 | Closed |
3 | Change Request | 1/3/2022 | Open |
4 | Bug | 1/4/2022 | Closed |
5 | Enhancement | 1/5/2022 | Closed |
6 | Bug | 1/6/2022 | Open |
7 | Bug | 1/7/2022 | Closed |
8 | Change Request | 1/8/2022 | Closed |
9 | Bug | 1/9/2022 | Open |
10 | Enhancement | 1/10/2022 | Closed |
Changelog:
Issue # | Change Type | Change Date |
1 | Status Change | 1/2/2022 |
1 | Comment | 1/3/2022 |
1 | Status Change | 1/4/2022 |
1 | Comment | 1/6/2022 |
2 | Status Change | 1/5/2022 |
2 | Comment | 1/6/2022 |
2 | Status Change | 1/7/2022 |
2 | Comment | 1/8/2022 |
2 | Status Change | 1/9/2022 |
3 | Comment | 1/10/2022 |
3 | Status Change | 1/2/2022 |
4 | Comment | 1/3/2022 |
4 | Status Change | 1/4/2022 |
4 | Comment | 1/6/2022 |
5 | Status Change | 1/5/2022 |
6 | Comment | 1/6/2022 |
6 | Status Change | 1/7/2022 |
6 | Comment | 1/8/2022 |
6 | Status Change | 1/9/2022 |
6 | Comment | 1/10/2022 |
6 | Status Change | 1/2/2022 |
7 | Comment | 1/3/2022 |
7 | Status Change | 1/4/2022 |
7 | Comment | 1/6/2022 |
7 | Status Change | 1/5/2022 |
8 | Comment | 1/6/2022 |
9 | Status Change | 1/7/2022 |
9 | Comment | 1/8/2022 |
9 | Status Change | 1/9/2022 |
9 | Comment | 1/4/2022 |
10 | Status Change | 1/6/2022 |
10 | Comment | 1/5/2022 |
Count Result:
Issue # | Changes |
1 | 4 |
2 | 5 |
3 | 2 |
4 | 3 |
5 | 1 |
6 | 6 |
7 | 4 |
8 | 1 |
9 | 4 |
10 | 2 |
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!
Solved! Go to Solution.
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 @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
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 #)
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.