Forum Discussion
Lookup rank value?
Hi Rohit - many thanks for your reply - much appreciated!
In the end I got rid of the ranking tables since think was getting a bit messy. For neatness, simplicity and speed I just used min and max in the main table and then created a measure to see where the specific score sat in this range (lower, middle, top third). It's not actually ranking in the strict sense but is an ok measure in this situation since the scores are bounded and generally well distributed. Thanks again!
hi worthywow1988 ,
Your goal is to display the Upper Quartile Label from the RANKING_TABLE in the TABLE_DATA_SUMMARY. Since LOOKUPVALUE is not working as expected, try using CALCULATE with FILTER instead.
1. Create a Measure for Lookup: Use CALCULATE to find the quartile label based on Area and Product:
Upper_Quartile_Label_Summary =
VAR SelectedArea = SELECTEDVALUE(TABLE_DATA[Area])
VAR SelectedProduct = SELECTEDVALUE(TABLE_DATA[Product])
RETURN
CALCULATE(
MAX(RANKING_TABLE[Upper Quartile Label]),
RANKING_TABLE[Area] = SelectedArea,
RANKING_TABLE[Product] = SelectedProduct
)
2. Add the Measure to TABLE_DATA_SUMMARY
- Place Upper_Quartile_Label_Summary in your TABLE_DATA_SUMMARY table.
- This will fetch the corresponding quartile label for the selected Area and Product.
Hi Rohit - many thanks for your reply - much appreciated!
In the end I got rid of the ranking tables since think was getting a bit messy. For neatness, simplicity and speed I just used min and max in the main table and then created a measure to see where the specific score sat in this range (lower, middle, top third). It's not actually ranking in the strict sense but is an ok measure in this situation since the scores are bounded and generally well distributed. Thanks again!
Hi,worthywow1988 . It's great to see that you solved your problem and that you shared the method to the forum,
you can mark your option as a solution which will help other users in the forum.I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian