topic Re: Calculate and Sum with Wildcard in DAX Commands and Tips

Calculate and Sum with Wildcard

thebeginner1204 — Tue, 04 Oct 2022 13:17:56 GMT

Hi there

is there a possibility to set a sum from a filter with wildcard using DAX?

I have a list of accounts.
I want to sum the accounts >=10xx* and <=109x*.
The accounts are formatted as numeric fields.
Without a wildcard the filter works, but the accounts are dynamic, so I am looking for a solution to make it so.

CALCULATE(

SUM('TBL'[Sales]),

FILTER(ALL(TBL[Accounts]),TBL[Accounts] >=10* && <=109*)

)

Thanks for a Tipp

Regards,

Re: Calculate and Sum with Wildcard

Greg_Deckler — Tue, 04 Oct 2022 13:39:57 GMT

thebeginner1204 Well, you don't need the * for the >10. Perhaps pick a reasonable maximum for the other? 109999?

Re: Calculate and Sum with Wildcard

tamerj1 — Tue, 04 Oct 2022 13:42:00 GMT

thebeginner1204
Please provide sample data along with the expected result.

Re: Calculate and Sum with Wildcard

thebeginner1204 — Tue, 04 Oct 2022 14:05:42 GMT

tamerj1 sure, here:

The following list contains accounts (Columm "Konto") which have to be calculated separately.
Marked red refers to the question in this case.
The filter should sum up all values in the column "Bewegung" which are either >=1000 <=1099 or >=10000 <= 10999 in the column "Konto", i.e. either between 1000 and 1099 or between 10000 and 10999.

This is how I tried it, the formula works, but the result is 0.

CALCULATE(
SUM('TBL'[Bewegung]),
FILTER(ALL(TBL[Konto]),TBL[Konto] >=1000 && TBL[Konto] <=1099 && TBL[Konto] >=10000 && TBL[Konto] <=10999)
)

Re: Calculate and Sum with Wildcard

thebeginner1204 — Tue, 04 Oct 2022 14:07:05 GMT

Greg_Deckler correct, i find a way for this

CALCULATE(
SUM('TBL'[Bewegung]),
FILTER(ALL(TBL[Konto]),TBL[Konto] >=1000 && TBL[Konto] <=1099 && TBL[Konto] >=10000 && TBL[Konto] <=10999)
)