DAX formula to find second minimum with filters

Question

I have asked a similar question a while back to which I received an answer at this page : DAX formula to find second minimum with extra criteria

My problem has changed slightly and I cannot find a solution.

I have a table with cities, types of expenses and Value of said expense. I want to create a calculated measure that finds the 2nd minimum value between the different cities for a given expense. Cities can be filtered down with a slicer and 2nd min needs to adjust to this change. The goal is to have a chart showing expenses and cities as rows and value/2ndmin as values. The chart would show the first expense on the left with all the cities and 2nd min value for these, the second expense with all the cities and 2nd min value for these etc. Chart should be able to show many expenses and 2nd min should adjust to any of these.

Some code was provided in the other post which worked fine for my first request, but I haven't been able to tweak it in order to make it work for this.

I work through power pivot on Excel which doesn't allow all functions like RemoveFilters, but I found that "All" seems to work the same for the purpose described in the other post.

Can anyone help ? Thank you very much !

Answer 1

This is the measure from the previous answer reworked to ignore categories and to use the slicer selection

SecondMin = 
VAR CurrentExpense =
    SELECTEDVALUE ( Costs[Expense] )
RETURN
    IF (
        NOT ISBLANK ( CurrentExpense ),
        VAR CostsSelected =
            FILTER ( ALLSELECTED ( Costs ), Costs[Expense] = CurrentExpense )
        VAR MinValue =
            MINX ( CostsSelected, Costs[Value] )
        VAR Min2Value =
            MINX ( FILTER ( CostsSelected, Costs[Value] > MinValue ), Costs[Value] )
        VAR Result =
            IF (
                COUNTROWS ( FILTER ( CostsSelected, Costs[Value] = MinValue ) ) > 1,
                MinValue,
                Min2Value
            )
        RETURN
            IF ( NOT ISEMPTY (Costs), Result + 0 )
    )

The resulting table adapts automatically to the slicer