Minimum row value based on visible columns

[Mareias]

Hello,

I have a table like the one below, showing the minimum freight cost for a specific route based on the origin. Each origin is a different column.

In my database I already have the best origin for each specific route, but I want to create something more dynamic.

The idea was to make the Power BI recalculate the best origin based on the visible columns only.

For example, in a database where I have the route A, B, C, D, E and origin F1, G1, H1, I1, J1, my database shows the best route as H1:

	F1	G1	H1	I1	J1	Best origin
A	967	875	825	843	849	H1
B	947	843	974	940	825	J1
C	939	820	971	960	875	G1
D	860	819	986	827	937	G1
E	886	853	835	877	898	H1

But, for the route A, I want power BI to consider I1 as the best origin if I remove H1 from the table

	F1	G1	I1	J1	Best origin
A	967	875	843	849	I1

I recently found a formula that brings me the best origin, but how should I adapt it in order to make it consider only the visible columns of a table that I will create?

Column =
VAR temp = {
        ( "Anchor1", Table1[Anchor_1] ),
        ( "Anchor2", Table1[Anchor_2] ),
        ( "Anchor3", Table1[Anchor_3] ),
        ( "Anchor4", Table1[Anchor_4] ) }
VAR MinValue =
    MINX ( FILTER ( temp, [Value2] <> 0 ), [Value2] )
RETURN
    MINX ( FILTER ( temp, [Value2] = MinValue ), [Value1] )

Thank you

[v-juanli-msft]

Hi @Mareias

Does your dataset structure as below?

Best Regards

Maggie

[Mareias]

Hello,

I have a table like the one below, showing the minimum freight cost for a specific route based on the origin. Each origin is a different column.

In my database I already have the best origin for each specific route, but I want to create something more dynamic.

The idea was to make the Power BI recalculate the best origin based on the visible columns only.

For example, in a database where I have the route A, B, C, D, E and origin F1, G1, H1, I1, J1, my database shows the best route as H1:

	F1	G1	H1	I1	J1	Best origin
A	967	875	825	843	849	H1
B	947	843	974	940	825	J1
C	939	820	971	960	875	G1
D	860	819	986	827	937	G1
E	886	853	835	877	898	H1

But, for the route A, I want power BI to consider I1 as the best origin if I remove H1 from the table

	F1	G1	I1	J1	Best origin
A	967	875	843	849	I1

I recently found a formula that brings me the best origin, but how should I adapt it in order to make it consider only the visible columns of a table that I will create?

Column =
VAR temp = {
        ( "Anchor1", Table1[Anchor_1] ),
        ( "Anchor2", Table1[Anchor_2] ),
        ( "Anchor3", Table1[Anchor_3] ),
        ( "Anchor4", Table1[Anchor_4] ) }
VAR MinValue =
    MINX ( FILTER ( temp, [Value2] <> 0 ), [Value2] )
RETURN
    MINX ( FILTER ( temp, [Value2] = MinValue ), [Value1] )

Thank you