approximate match lookupvalue

[Anonymous]

Hello,

How do I get a Rating in Table 2 using lookupvalue with the data sets I have in table 1.

Thanks in advance!

[mangaus1111 · Accepted solution]

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.

[Anonymous · Accepted solution]

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. 

[Anonymous · Accepted solution]

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. 

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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!

[Anonymous · Accepted solution]

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

[mangaus1111 · Accepted solution]

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.