Forum Discussion

srennie's avatar
srennie
Regular Visitor
3 years ago
Solved

Using SELECTEDVALUE to rank data in a table

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.

 

RankOpen Order AmtFirst Pass YeildDefect CountDays Since Last Audit
Rank 11849596.30%1204
Rank 212114388.89%3419
Rank 348519350.00%6734
Rank 417592770.00%181186

 

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])
        )
    )

 

 

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    3 years ago

    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

1 Reply

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    3 years ago

    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