Forum Discussion
How to order slicer values by another column within each group of the current column
Hi everyone,
I have a table that looks like this:
WELL FORMATION DEPTH
| A | B_top | 1 |
| A | A_top | 2 |
| A | C_top | 3 |
| B | B_top | 45 |
| B | A_top | 48 |
| B | C_top | 50 |
| C | B_top | 101 |
| C | A_top | 120 |
| C | C_top | 135 |
In Power BI I want to build a slicer for FORMATION.
The issue: the slicer always shows the formations alphabetically, but I want them ordered by their depth within each well.
I tried creating a Formation_Order column using RANKX, but Power BI gives the error "There can’t be more than one value in Formation_Order for the same value in FORMATION" (because the same formation appears in multiple wells at different depths).
What’s the correct way to set up a slicer that respects the per-well depth order? Do I need to build a separate dimension table for slicer values, with WELL + FORMATION combined, and then sort by depth? Or is there a simpler trick to achieve this?
Thanks a lot for your help!
3 Replies
Sorting by depth should be sufficient.
What's the benefit of the slicer?
The good thing about slicer is that it doesn't show me the WELL names, I can just drop in FORMATIONs and then filter all the other plots by selecting any formation. In the example you have provided it shows all the wells, which I have 100s of.
You can not sort the formation on well depths if you don't include the wells.
