dax - DAX formula to find second minimum with extra criteria

Question

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 !

标签： daxpowerpivot

Answer 1

这很棘手，因为最小值的关系。解决方案是计算具有最小值的行，如果超过一个返回最小值，否则第二个到最小值

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 )