Forum Discussion

AxelKAp's avatar
AxelKAp
Icon for Helper I rankHelper I
3 years ago

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 NumberInvoice DateInvoice AmountSupplier NameEntity
A12301/01/2022100000€AFrance
B12301/01/202220000€CFrance
C12301/01/20221500€DGermany
D12301/01/2022260000€DSpain
E12301/01/202270000€EItaly
G12301/01/2022200€BUK


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 !