Using SELECTEDVALUE to rank data in a table

[srennie]

I have created a table using percentiles from my data that I want to use to give values in another table a rank - ie, if defect count is equal to or less than 1 then rank 1, if  equal to or less than 3 then rank 2, above 18 would be rank 5 etc.

Rank	Open Order Amt	First Pass Yeild	Defect Count	Days Since Last Audit
Rank 1	18495	96.30%	1	204
Rank 2	121143	88.89%	3	419
Rank 3	485193	50.00%	6	734
Rank 4	1759277	0.00%	18	1186

I've tried to below DAX which I've used successfully before on another dataset but I'm not getting a result. What am I doing wrong?

Defect Risk = 
    CALCULATE(
        SELECTEDVALUE('Rank Index'[Rank]),
        FILTER('Rank Index',
        'Rank Index'[Defect Count] > (Risk_Index_Data[Count of Defects])
        'Rank Index'[Defect Count] < (Risk_Index_Data[Count of Defects])
        )
    )

[v-easonf-msft · Accepted solution]

Hi, @srennie 

filter('Rank Index','Rank Index'[Defect Count] > (Risk_Index_Data[Count of Defects]))

The above filter returns multiple records. You need to use aggregate function (min, max,sum) instead of SELECTEDVALUE to get result.

Please try formula like

Defect Risk =
CALCULATE (
    MIN ( 'Rank Index'[Rank] ),
    FILTER (
        'Rank Index',
        'Rank Index'[Defect Count] >= Risk_Index_Data[Count of Defects]
    )
)

Best Regards,
Community Support Team _ Eason