Forum Discussion
approximate match lookupvalue
Hello,
How do I get a Rating in Table 2 using lookupvalue with the data sets I have in table 1.
Thanks in advance!
Hi Anonymous ,
why don´t you use function SWITCH, instead of LOOKUPVALUE?
SWITCH(TRUE(), 'Table1'[Attendence] < 0,9, 1, 'Table1'[Attendence] < 0.95, 2, Table1'[Attendence] < 0.98, 3, Table1'[Attendence] < 1, 4, 5 )If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you mangaus1111 for the solution. Yes this works.
I'm using lookupvalue since the rating might change the next month and I don't want the historical rating to be affect by the switch Dax. Do we have solution to that? Appreciate your help.
6 Replies
Hi Anonymous ,
why don´t you use function SWITCH, instead of LOOKUPVALUE?
SWITCH(TRUE(), 'Table1'[Attendence] < 0,9, 1, 'Table1'[Attendence] < 0.95, 2, Table1'[Attendence] < 0.98, 3, Table1'[Attendence] < 1, 4, 5 )If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you mangaus1111 for the solution. Yes this works.
I'm using lookupvalue since the rating might change the next month and I don't want the historical rating to be affect by the switch Dax. Do we have solution to that? Appreciate your help.
Hello @Xiaoxin,
Appologies for the confusion. I mean the range of the rating in Table 2 might change in the future and we don't want that affect the historical data if we're using switch dax. For example next month it might change to, 5 = (100%), 4 = (<100%-97%), 3 = (<97%-94%) and so on.
Regards,
Boy Coronacion
HI Anonymous,
You can refer to the following blog to create a dynamic segmentation table and write ranking formula based on this table definition.
These ranking results will also automatically changes when you modify the segmentation table ranges.
Dynamic segmentation – DAX Patterns
Regards,Xiaoxin Sheng