The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 🙂
User | Count |
---|---|
80 | |
73 | |
40 | |
30 | |
28 |
User | Count |
---|---|
107 | |
96 | |
55 | |
47 | |
47 |