How do I use MIN or MINX to find the lowest value according to a filtered column

[nschmidt]

I have a sample data set below. How would I use a dax function to find the MIN of the rank column according to the part number. For example, I want to calculate the cost difference of the most recent rank which is 5 vs. the minimum rank. I can't just use the MIN function because it will automatically go to Rank 1. For part number 76554 I need the MIN function to look at the Rank column filtered down to the value in part number of 76554 and return 2 as my minimum rank.

Part Number	Rank	Cost
407563	1	$      1.25
407563	2	$      1.25
407563	3	$      1.25
407563	4	$      1.25
407563	5	$      1.35
76554	2	$      1.45
76554	3	$      1.55
76554	4	$      1.65
76554	5	$      1.00
99008766	3	$      1.00
99008766	4	$      1.00
99008766	5	$      1.10

[Fowmy · Accepted solution]

@nschmidt 

Add this as a measure;

Variance = 
VAR _MinR = CALCULATE( min(Table9[Rank]), ALLEXCEPT(table9,Table9[Part Number]))
VAR _MaxR = CALCULATE( max(Table9[Rank]), ALLEXCEPT(table9,Table9[Part Number]))
return
CALCULATE( 
    MAX(Table9[Cost]),
    Table9[Rank] = _MaxR,
    ALLEXCEPT(table9,Table9[Part Number])
) - 
CALCULATE( 
    MAX(Table9[Cost]),
    Table9[Rank] = _MinR,
    ALLEXCEPT(table9,Table9[Part Number])
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn