Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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]))))
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]))))
Worked perfect, thank you 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |