topic Re: Create Categories Based On Count From a Measure in DAX Commands and Tips

Create Categories Based On Count From a Measure

JJ51 — Fri, 18 Mar 2022 17:19:42 GMT

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!

Re: Create Categories Based On Count From a Measure

Anonymous — Fri, 18 Mar 2022 17:40:30 GMT

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.

Re: Create Categories Based On Count From a Measure

tamerj1 — Sun, 20 Mar 2022 08:34:34 GMT

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

Re: Create Categories Based On Count From a Measure

JJ51 — Mon, 21 Mar 2022 16:34:21 GMT

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

Re: Create Categories Based On Count From a Measure

Anonymous — Fri, 02 Jun 2023 00:31:06 GMT

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 ?

Re: Create Categories Based On Count From a Measure

tamerj1 — Fri, 02 Jun 2023 08:43:18 GMT

Anonymous
Would you please place a screenshot?