Hello guys,
I am creating a dashboard with a search filter. I created a virtual column "final balance" that has this expression:
SUM (
SELECT (ESTBC [taxable invoice],
[Paid] = SEARCH [Paid]))
Where the [Paid] field from the SEARCH table is of type EnumList with two values ("paid"; "unpaid") I would like to be able to select both "paid" and "unpaid" values. With this expression the filter only works if you select one of the two values.
How can I modify the expression to filter the values that are both "paid" and "unpaid"?
TKS
Solved! Go to Solution.
SUM(
SELECT(
ESTBC[taxable invoice],
IN([Paid], SPLIT(("" & SEARCH[Paid]), " , "))
)
)
@SkrOYC Hi, can you explain me how can i use these expression in mine? I'll appreciate it
SUM(
SELECT(
ESTBC[taxable invoice],
IN([Paid], SPLIT(("" & SEARCH[Paid]), " , "))
)
)
("" & SEARCH[Paid]) is the same as CONCATENATE(SEARCH[Paid]). Both convert the list of values represented by SEARCH[Paid] into a single textual value of the same date. When a list is converted to text, the list elements are separated by the separator configured for the column (space-comma-space by default), ensuring a known separator value we can then use with SPLIT(). If you don't specifically convert the list to text, a different separator may be used to join the items, which confuses SPLIT().
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |