Value range lookup - M Query

[jswwood]

Hi

Im trying to determine a price bracket by using a looku table which has a price from column and a price to e.g.

Price Bracket     Price From   Price To

100K - 125K      100000        125000

I have tried merging the lookup table as a cartesian join to my trans table then creating a flag column to filter by eg "If Price >= [Price From] and Price <= [Price To] then 1 else 0 (Filter = 1). It works but as anticipated runs like a 'dog', taking hours to process. Is there a slicker way please rather than resorting to {if price >= 100,000 and price <= 125000 then "100k-125k" elseif price >= 125001 and .... etc etc

Thank you in anticipation!

[mahoneypat · Accepted solution]

This is much easier to do with DAX in a column or a measure.  Please see this post for an example.

https://forum.enterprisedna.co/t/dynamic-bin-segmentation/1250/2

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[jswwood]

 Hi Pat

Thanks for the reply. Ive marked as solution but I am looking for an M Query solution with a view to Users not having to get involved in more complex Functions. I will carry out some performance tests as it may be the best route.

Thank you again.

Kind Regards

John