Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Thank you, Zain
Solved! Go to Solution.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |