DAX formula to find second minimum with extra criteria

user_5139390 0
Viewed 128

I have a problem that involves two tables, one with some costs (table stops at value, I have put in Expected Value what I want to see in my explicit measure), one with cities sorted by clusters.

I need to create an explicit measure that returns the second minimum value for a given cluster and Expense.

Costs Table

Date City Expense Value Expected Value
2020 Paris Electricity 1 1
2020 Berlin Electricity 1 1
2020 London Electricity 2 1
2020 New York Electricity 0 0
2020 Paris Heating 1 4
2020 Berlin Heating 4 4
2020 London Heating 12 4
2020 New York Heating 7 7

Cluster Table

City Cluster
Paris Europe
London Europe
Berlin Europe
New York America

Let's take the expected value for [Electricity] in [Europe] Cluster. I get a 1 as two lowest values are 1. For Heating, I get a 4 as second minimum value is a 4. (New York returns zero for the purpose of example but clusters will not contain only one city.)

For now, I have this bit of code which doesn't take into account the Expense type and Cluster :

2ndMin:=minX(
        topN(countrows(ALL(Costs))-1;ALL(Costs);Costs[Value])
        ;Costs[Value])

Any idea how I should edit the code to include the two specifications above ?

Thanks !

powerpivot dax
1 Answers

This is tricky, because of ties for the min value. The solution is to count the rows with the minimum value and if more than one return the minimum, else the second to minimum

SecondMin =
VAR CurrentCluster =
    SUMMARIZE( 'Costs', 'Cluster'[Cluster] )
VAR CurrentExpense =
    VALUES( Costs[Expense] )
VAR CostsPerClusterAndExpense =
    CALCULATETABLE(
        Costs,
        CurrentCluster,
        CurrentExpense,
        REMOVEFILTERS( 'Cluster' ),
        REMOVEFILTERS( 'Costs' )
    )
VAR MinValue =
    MINX( CostsPerClusterAndExpense, Costs[Value] )
VAR Min2Value =
    MINX(
        FILTER( CostsPerClusterAndExpense, Costs[Value] > MinValue ),
        Costs[Value]
    )
VAR Result =
    IF(
        COUNTROWS( FILTER( CostsPerClusterAndExpense, Costs[Value] = MinValue ) ) > 1,
        MinValue,
        Min2Value
    )
RETURN
    IF( NOT ISEMPTY( Costs ), Result + 0 )
user_2489490
Related