Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Measure a field between two values MIN and MAX

Hi !

I'm trying to measure a field between two values ...for example,

I have a record with a counter that has the value 7 and I have a group (the groups are not in dynamics365) "bucketB"(which has a MIN(column) with value 1 and a MAX(column) with value 35) so that record should be a part of the group "BucketB".

So all records that have a counter of the value 0 should end up in BucketA(0), all records with a counter with the value 1 to 35 should end up in BucketB and so on.

..... Now I made a table "Bucket" with the columns and rows but I'm kinda stuck. Because I don't really have a relation with the main table that has the record and the counter. I tried some similar solutions with DAX but no luck (Its maybe I suck at DAX)

... some pictures to make it more clear what I'm trying to achieve

 

The Table I made (not in relation with the main table where the record and the counter are)What I'm trying to achieve

 

Thank you, Zain

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi Anonymous ,

    My original table:

    //Column
Bucket = 
SWITCH(
    TRUE(),
    [Score] = 0 , "0",
    [Score] >= 1 && [Score] <= 30, "1-30",
    [Score] >= 31 && [Score] <= 45, "31-45",
    [Score] >= 46 && [Score] <= 60, "46-60",
    [Score] > 60, "61+"
)

Min_ = 
SWITCH(
    TRUE(),
    [Score] = 0 , 0,
    [Score] >= 1 && [Score] <= 30, 1,
    [Score] >= 31 && [Score] <= 45, 31,
    [Score] >= 46 && [Score] <= 60, 46,
    [Score] > 60, 61
) 

Max_ = 
SWITCH(
    TRUE(),
    [Score] = 0 , 0,
    [Score] >= 1 && [Score] <= 30, 30,
    [Score] >= 31 && [Score] <= 45, 45,
    [Score] >= 46 && [Score] <= 60, 60,
    [Score] > 60, 150
)

Sort Order = 
RANKX(
    Sheet6,
    [Min_],
    ,
    ASC,
    Dense
)

//Measure
number = 
CALCULATE(
    COUNT(Sheet6[Score]),
    ALLEXCEPT(
        Sheet6,
        Sheet6[Bucket]
    )
)

     

     

    Best regards,
    Lionel Chen

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

    My original table:

    //Column
Bucket = 
SWITCH(
    TRUE(),
    [Score] = 0 , "0",
    [Score] >= 1 && [Score] <= 30, "1-30",
    [Score] >= 31 && [Score] <= 45, "31-45",
    [Score] >= 46 && [Score] <= 60, "46-60",
    [Score] > 60, "61+"
)

Min_ = 
SWITCH(
    TRUE(),
    [Score] = 0 , 0,
    [Score] >= 1 && [Score] <= 30, 1,
    [Score] >= 31 && [Score] <= 45, 31,
    [Score] >= 46 && [Score] <= 60, 46,
    [Score] > 60, 61
) 

Max_ = 
SWITCH(
    TRUE(),
    [Score] = 0 , 0,
    [Score] >= 1 && [Score] <= 30, 30,
    [Score] >= 31 && [Score] <= 45, 45,
    [Score] >= 46 && [Score] <= 60, 60,
    [Score] > 60, 150
)

Sort Order = 
RANKX(
    Sheet6,
    [Min_],
    ,
    ASC,
    Dense
)

//Measure
number = 
CALCULATE(
    COUNT(Sheet6[Score]),
    ALLEXCEPT(
        Sheet6,
        Sheet6[Bucket]
    )
)

     

     

    Best regards,
    Lionel Chen

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.