Forum Discussion
Help on a SWITH() and SELECTEDVALUE() formula
Hi everyone,
I need your help to set up a DAX formula to filter by thresholds.
I have a table where you can find all the invoice informations, below you can find how the tha table is build:
| Invoice Number | Invoice Date | Invoice Amount | Supplier Name | Entity |
| A123 | 01/01/2022 | 100000€ | A | France |
| B123 | 01/01/2022 | 20000€ | C | France |
| C123 | 01/01/2022 | 1500€ | D | Germany |
| D123 | 01/01/2022 | 260000€ | D | Spain |
| E123 | 01/01/2022 | 70000€ | E | Italy |
| G123 | 01/01/2022 | 200€ | B | UK |
I have second table, where you can find the thresholds, I would like to filter by this values:
| Thresholds |
| <20K€ |
<50K€ |
| <100K€ |
| <250K€ |
| <500K€ |
| >500K€ |
My goal is to filter by threshold, the total supplier spend, example:
If I filter with <500K€, I will see only the suppliers with a total spend <500K€. And I have to be able to filter by year, entity, ...
Total spend is the sum of all the invoice amount for a dedicated supplier.
I have created this formula:
Thresholds =
var _spend = sum(Invoice Amount)
return
SWITCH(SELECTEDVALUE(Threshold[Thresholds]), "<20K€", _spend < 20000, "<50K€", _spend < 50000, "<50K€", "<100K€", _spend < 100000, "<250K€", _spend < 250000, "<500K€", _spend < 500000, ">500K€", _spend > 50000, BLANK())
But when I try to filter by threshold, nothing happens. Do you have an idea on what is wrong ?
Thanks for your support !