Create a measure to lookup a value in an unrelated table using values from another measure?

[hyggins]

I’ve been scouring the forums and unable to find anything that matches my needs.

I have two tables, one is an accounting transaction table [tblAccountingGL], the other is a commission lookup table [tblComLookup]. There is no relationship in the data model.

We sum the sales transaction values by employee by month. This is done with a measure called [TotalSales]. This monthly total is what determines their commission %.

For example, if they sell between $0 and $9,999 the commission is 3%. Sales between $10,000 and $24,999 it’s 4%, etc.

I need to create a measure that returns the Commission % based on the value of the measure [TotalSales].

Example: If [TotalSales] for February = 16,000 then return a value of 4%. The [TotalSales] value of $16,000 is between $10,000 and $24,999 as represented in the commission table so 4% is returned.

Commission Table Sample

Record Id	Min Sales Value	Max Sales Value	Commission %
1	0	9999	3%
2	10000	24999	4%
3	25000	50000	5%

Thank you!

[Anonymous · Accepted solution]

You can use SWITCH in table  [tblAccountingGL] itself with the assumption table [tblComLookup] is not dynamic
=SWITCH (
  TRUE (),
 [TotalSales]>0 && [TotalSales]<=999,0.03,
[TotalSales]>=10000 && [TotalSales]<=24999,0.04,
and so on.....
)

If table [tblComLookup] is dynamic
You can create a range of values using SWITCH
SWITCH (
TRUE(),
[TotalSales]>0 && [TotalSales]<=999,"0-999",
[TotalSales]>=10000 && [TotalSales]<=24999,"10000-24999",
and so on.....

and for [tblComLookup], join both Min and Max sales values [Min Sales value] & "-" & [Max Sales Value].
then create relationship between the newly created columns and you can get your commission %. 

Hope it works. Pls let me know if any suggestions/corrections.