Create column value from non related table

[mikkelbn]

I am new to power BI and cannot get this to work:

I have a table: Customer ID | Customer | Value | Level - the first three are populated and the last needs to be populated lookup in another table: Level | From | To - meaning the if the  Value from first table is between From and To in the second table the Level should be set. Is that doable?

[Anonymous · Accepted solution]

You can filter your second table for each row on the first table, if the result of filtering is a single row, you can use any expressions with "first" or "last" to get this row.

Try this formula on CustomerTable[Level]:

Customer Level = CALCULATE( LASTNONBLANK('LevelTable'[Level];1) 
		; FILTER( 'LevelTable';'LevelTable'[From]= CALCULATE(
			LASTNONBLANK('LevelTable'[From];1)
			;FILTER('LevelTable';'LevelTable'[From]<= 'CustomerTable'[Value] 
				&& 'LevelTable'[To]>='CustomerTable'[Value]))))

[mikkelbn]

Worked perfect, thank you 🙂