How to order slicer values by another column within each group of the current column

[kamab]

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!

[lbendlin]

Sorting by depth should be sufficient.

What's the benefit of the slicer?

[kamab]

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.

[lbendlin]

You can not sort the formation on well depths if you don't include the wells.