Forum Discussion
approximate match lookupvalue
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.
HI Anonymous,
Did you mean these new records will be added to the current table and affect the ranking? If that is the case, current power bi does not exist historical data feature. You may need to export to the current records with ranking to keeping results.
Regards,
Xiaoxin Sheng
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
Sure, I'll check on this one. But it seems we still don't have the approximate match function like in Vlookup in excel. Thank you!