Forum Discussion

adriancho_BI's avatar
adriancho_BI
Regular Visitor
2 years ago
Solved

Create Categories

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%" )



Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    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%"
    )
)

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    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's avatar
      adriancho_BI
      Regular Visitor
      2 years ago

      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's avatar
        Jihwan_Kim
        Super User
        2 years ago

        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%"
    )
)