Lookup the smallest value and return name associated with that value

[akarasick1]

I have a dataset that looks like the table below. I'm trying to build a measure that will let me calculate the smallest difference between an amount and any other amount in that table. I'd then like to return the name that generated the smallest value.

Name	Amount
A	1
B	4
C	9
D	16

For example:

  For Name A, the minimum difference calculated is 3 (4-1; 9-1; 16-1) and that corresponds with B.

  For Name C, the minimum difference calculated is -8 (1-9; 4-9; 16-9) which corresponds with A.

Any idea on how I can create a measure that does the amount difference calculation iterating across the entire table (all other amounts less current row amount) as well as a measure that returns the corresponding name for that minimum difference?

Thank you in advance

[mahoneypat]

I believe this measure will do what you are looking for.  I made a table called 'Data' with your values.  Also included comments on each line to explain how it works.

Min Letter = var currentletter = SELECTEDVALUE(Data[Name]) // store current letter/Name

var currentlettervalue = SELECTEDVALUE(Data[Amount]) // store the current Amount for this letter

var differences = FILTER(ADDCOLUMNS(All(Data[Name], Data[Amount]), "Difference", Data[Amount]-currentlettervalue), Data[Name]<>currentletter) // make a table with all data and the difference from current value; Filter out the row for current letter

var minletter = CALCULATE(MIN(Data[Name]), TOPN(1, differences,[Difference],ASC)) // get the Name value where difference is the lowest

return minletter

[harshnathani]

Hi @akarasick1 ,

Its quite a tricky problem and requires to be solved via relationship.

Your Original Table:

Table2

1. Create a new Table:

New Table = ADDCOLUMNS(

ADDCOLUMNS(

SUMMARIZE(

Table2,

Table2[Name],

Table2[Amount]

),

"Minimim",

If(MIN(Table2[Amount]) = Table2[Amount],

CALCULATE(Min(Table2[Amount]),FILTER(Table2,Table2[Amount]>EARLIER(Table2[Amount]))),

CALCULATE(MIN(Table2[Amount]),FILTER(Table2,Table2[Amount]<=EARLIER(Table2[Amount]))))

),

"Difference", [Minimim]-Table2[Amount])

3. Create a Summary Table:

Summary Table = SUMMARIZE('New Table','New Table'[Amount],'New Table'[Name])

4. Manage Relationship

5. Visualization

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

[akarasick1]

Thank you for all of your effort! I'm worried that joining based on amount will cause some issues if the amounts are the same for more than one value. Any thoughts on that?