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 ign...
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 #] )
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?