Help on a SWITH() and SELECTEDVALUE() formula

[AxelKAp]

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 !

[FreemanZ]

hi @AxelKAp  

Is Threshold a calculated column or a measure?

[AxelKAp]

Hello @FreemanZ 

It's a measure, not a calculated column

[AxelKAp]

Hello @FreemanZ , 

Any idea ? 

Thanks for your support 

[FreemanZ]

hi @AxelKAp 

what is your expected result, based on your sample data?

[AxelKAp]

Hi @FreemanZ ,

With my sample data, I would like to filter by spend thresholds.
Example, if I filter by "<10K€", I will only have the suppliers with a Total Spend "<10K€", here I will have only supplier B.