Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 !
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.
User | Count |
---|---|
47 | |
44 | |
19 | |
14 | |
14 |
User | Count |
---|---|
107 | |
56 | |
28 | |
20 | |
16 |