Create Categories

[adriancho_BI]

Hi! I need your help. 
I'm trying to create a category measure, where the column I'm referencing has numers from 0 to 1. I'm trying to create a category every 25% like ranges. I cant't manually group thems because I have hundreds of thounsends differents values. Thanks Chat GPT and youtube I have these tow approaches, but no one have resolved my issue, thanks in advance for the help!

Approach 1:

IF(

    sumx(sign_up_data_device,sign_up_data_device[%_days_with_traffic]) = 0,

    "0%",

    IF(

       sumx(sign_up_data_device,sign_up_data_device[%_days_with_traffic]) <= 0.25,

        ">0 -25%",

        IF(

            sumx(sign_up_data_device,sign_up_data_device[%_days_with_traffic]) <= 0.5,

            ">25 -50%",

            IF(

                sumx(sign_up_data_device,sign_up_data_device[%_days_with_traffic]) <= 0.75,

                ">50 -75%",

                IF(

                    sumx(sign_up_data_device,sign_up_data_device[%_days_with_traffic]) > 0.75,

                    ">75-100%",

                    "0%"

                )

            )

        )

    )

)

Approach 2:
% Days with Traffic (Range) = VAR TrafficPercentage = SUMX(sign_up_data_device, sign_up_data_device[%_days_with_traffic]) RETURN SWITCH ( TRUE(), TrafficPercentage = 0, "0%", TrafficPercentage <= 0.25, ">0 -25%", TrafficPercentage <= 0.5, ">25 -50%", TrafficPercentage <= 0.75, ">50 -75%", TrafficPercentage > 0.75, ">75-100%", "0%" )

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file whether it suits your requirement.

SWITCH function (DAX) - DAX | Microsoft Learn

Category % CC =
SWITCH (
    TRUE (),
    data[value] = 0, "0%",
    data[value] <= 0.25, ">0 -25%",
    data[value] <= 0.5, ">25 -50%",
    data[value] <= 0.75, ">50 -75%",
    data[value] <= 1, ">75-100%"
)

[adriancho_BI]

It's a great aproximation of how my data looks like, something a I forgot to write, is that I'm working in Direct Query mode, where the creating custom column function is a litle restricted.

[Jihwan_Kim · Accepted solution]

Hi, if the measure is needed, please check the below picture and the attached pbix file.

Category % measure = 
IF (
    HASONEVALUE ( data[id] ),
    SWITCH (
        TRUE (),
        SUM ( data[value] ) = 0, "0%",
        SUM ( data[value] ) <= 0.25, ">0 -25%",
        SUM ( data[value] ) <= 0.5, ">25 -50%",
        SUM ( data[value] ) <= 0.75, ">50 -75%",
        SUM ( data[value] ) <= 1, ">75-100%"
    )
)