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 have the following example data:
ID | company | value | tier |
1 | A | 1 | tier 1 |
3 | A | 5 | tier 3 |
4 | A | 2 | tier 2 |
8 | A | 3 | tier 2 |
7 | B | 2 | tier 2 |
Tier is a calculated column that has the following syntax:
tier =
SWITCH (
TRUE (),
table[value] < max(table[value]) * 0.25, "tier 1",
table[value] < max(table[value]) * 0.50, "tier 2",
table[value] < max(table[value]) * 0.75, "tier 3",
"tier 4"
)
If i filter my data through slicers then not all rows of my data are being considered, meaning that instead of recalculating tiers for the filtered data, one or more of my tiers go missing. For example, if i use the slicers and choose only 2 companies:
ID | company | value | tier |
1 | A | 1 | tier 1 |
3 | A | 5 | tier 3 |
4 | A | 2 | tier 2 |
8 | A | 3 | tier 2 |
7 | B | 2 | tier 2 |
I have no tier 4, because the max value is a company that is not selected. I've tried creating a secondary filtered table but i'm having trouble applying the filters through the slicers. How can i make this dynamic in order to always have all the tiers available?
Thanks.
Solved! Go to Solution.
Hi @cdcphist,
Sorry for delay reply.
I'm not sure your expected output.
Do you want to sort the value under each tier?
Please try calculated column as:
Rank_tier =
var _tier='Table'[tier]
return
RANKX ( FILTER ( 'Table', 'Table'[tier] =_tier),'Table'[value] )
Best Regards,
Link
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @cdcphist,
Sorry for delay reply.
I'm not sure your expected output.
Do you want to sort the value under each tier?
Please try calculated column as:
Rank_tier =
var _tier='Table'[tier]
return
RANKX ( FILTER ( 'Table', 'Table'[tier] =_tier),'Table'[value] )
Best Regards,
Link
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Bump!
@cdcphist , Based on what I got, Try a measure like
tier =
var _max = maxX(all(Table), table[value])
SWITCH (
TRUE (),
table[value] < _max * 0.25, "tier 1",
table[value] < _max * 0.50, "tier 2",
table[value] < _max * 0.75, "tier 3",
"tier 4"
)
Hi @amitchandak ,
Thank you. I tried your suggestion but i get an error that a single value for "table[value]" cannot be determined.
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 |
---|---|
114 | |
100 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |