Forum Discussion

mikkelbn's avatar
mikkelbn
New Member
9 years ago
Solved

Create column value from non related table

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: L...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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]))))

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
mikkelbn
New Member
9 years ago

Worked perfect, thank you :)