cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## which calculated column has the closest value to another column

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?

 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)
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: which calculated column has the closest value to another column

@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],
", "
)```
6 REPLIES 6
Frequent Visitor

## Re: which calculated column has the closest value to another column

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.

Community Support Team

## Re: which calculated column has the closest value to another column

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: which calculated column has the closest value to another column

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

Super User

## Re: which calculated column has the closest value to another column

@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
)
)```
Super User

## Re: which calculated column has the closest value to another column

@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],
", "
)```
Community Support Team

## Re: which calculated column has the closest value to another column

Hi @BartB,

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.