Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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) |
Solved! Go to Solution.
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], ", " )
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
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
Hi @BartB,
Did @Zubair_Muhammad's solution work?
Best Regards,
Dale
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 ) )
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], ", " )
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
105 | |
69 | |
67 | |
43 |
User | Count |
---|---|
148 | |
103 | |
103 | |
88 | |
66 |