which calculated column has the closest value to another column

[BartB]

Hi all,

I've got 5 calculated columns and one column which I consider as my "Lookup value". Now I would like to make two more calculated columns:

1. with the value of the column that is closest to my lookup value

2. with the column name of the column that is closest to my lookup value.

I did something similar in Excel (see below). Does anyone know a DAX formula (or another solution) which I can use in Power BI in order to replicate what I've done in Excel?

Many thanks in advance!

Lookup value		column1	column 2	column3	column4	column5		Closest column	Value closest column
4		1	2	3	4	5		column4	4
								=LOOKUP(G5,I5:M5,I4:M4)	=LOOKUP(G5,I5:M5,I5:M5)

[v-jiascu-msft]

Hi @BartB,

1. Is the Lookup value stable? Always in a column?

2. Where is the lookup value? In another table?

3. Is there only one Lookup value? Or each row has a Lookup value?

4. How to evaluate the closest?

Best Regards,

Dale

[BartB]

Hi @v-jiascu-msft,

Thanks for your reply.

1. Is the Lookup value stable? Always in a column?

Yes, the lookup value is stable. It's a calculated column with the result of a lookup value to another column

2. Where is the lookup value? In another table?

The lookup value itself is in the same table. However, it's a calculated column and thus it's not in the Query Editor.

3. Is there only one Lookup value? Or each row has a Lookup value?

Each row has a lookup value

4. How to evaluate the closest?

Ideal would be literally closest to... (3.9 is closer to 4 than 4.2). But if this is not possible I would already be sattisfied with an approximate match as in the vlookup formula in Excel.

If you have any further questions, let me know.

BR,
Bart

[v-jiascu-msft]

Hi @BartB,

Did @Zubair_Muhammad's solution work?

Best Regards,

Dale

[Zubair_Muhammad]

@BartB

Give this a shot

Closest Value =
VAR fig = 1000000000
VAR Positives =
    UNION (
        ROW (
            "BartB", IF (
                Table1[Lookup value] >= Table1[column1],
                Table1[Lookup value] - Table1[column1],
                fig
            )
        ),
        ROW (
            "BartB", IF (
                Table1[Lookup value] >= Table1[column2],
                Table1[Lookup value] - Table1[column2],
                fig
            )
        ),
        ROW (
            "BartB", IF (
                Table1[Lookup value] >= Table1[column3],
                Table1[Lookup value] - Table1[column3],
                fig
            )
        ),
        ROW (
            "BartB", IF (
                Table1[Lookup value] >= Table1[column4],
                Table1[Lookup value] - Table1[column4],
                fig
            )
        ),
        ROW (
            "BartB", IF (
                Table1[Lookup value] >= Table1[column5],
                Table1[Lookup value] - Table1[column5],
                fig
            )
        )
    )
VAR Positivevariances =
    MINX ( Positives, [BartB] )
VAR Negatives =
    UNION (
        ROW (
            "BartB", IF (
                Table1[Lookup value] < Table1[column1],
                Table1[Lookup value] - Table1[column1],
                - fig
            )
        ),
        ROW (
            "BartB", IF (
                Table1[Lookup value] < Table1[column2],
                Table1[Lookup value] - Table1[column2],
                - fig
            )
        ),
        ROW (
            "BartB", IF (
                Table1[Lookup value] < Table1[column3],
                Table1[Lookup value] - Table1[column3],
                - fig
            )
        ),
        ROW (
            "BartB", IF (
                Table1[Lookup value] < Table1[column4],
                Table1[Lookup value] - Table1[column4],
                - fig
            )
        ),
        ROW (
            "BartB", IF (
                Table1[Lookup value] < Table1[column5],
                Table1[Lookup value] - Table1[column5],
                - fig
            )
        )
    )
VAR Negativevariances =
    MAXX ( Negatives, [BartB] )
RETURN
    IF (
        Positivevariances = 0,
        Table1[Lookup value],
        IF (
            Positivevariances <= ABS ( Negativevariances ),
            Table1[Lookup value] - Positivevariances,
            Table1[Lookup value] - Negativevariances
        )
    )

[Zubair_Muhammad · Accepted solution]

@BartB

Closest Column =
VAR MyTable =
    UNION (
        ROW ( "Column Name", "Column1", "Value", Table1[column1] ),
        ROW ( "Column Name", "Column2", "Value", Table1[column2] ),
        ROW ( "Column Name", "Column3", "Value", Table1[column3] ),
        ROW ( "Column Name", "Column4", "Value", Table1[column4] ),
        ROW ( "Column Name", "Column5", "Value", Table1[column5] )
    )
RETURN
    CONCATENATEX (
        FILTER ( MyTable, [Value] = Table1[Closest Value] ),
        [Column Name],
        ", "
    )

[dani0010]

You can do this with Power Query, but not DAX. Sorry I do not have time to provide a full solution right now, but I thought this tip might give you hope and save you time finding a solution.