Best Way to Implement a Filter for non Calculations:

[Anonymous]

I am trying to establish a fund balance based off a combination of 3 digit funds.  My current formula is this:

Balance = CALCULATE( SUMX ('UtilityCheck',[CY-AMT]),'Accounts'[Full Code] = "10400-0") +

                   CALCULATE( SUMX('UtilityCheck',[CY-AMT]),Accounts[Full Code] = "10520-0") -

                    CALCULATE( SUMX('UtilityCheck',[CY-AMT]),Accounts[Full Code] = "10521-0") -

                      CALCULATE( SUMX('UtilityCheck',[CY-AMT]),Accounts[Full Code] = "10570-0") -

                         CALCULATE( SUMX('UtilityCheck',[CY-AMT]),Accounts[Full Code] = "10571-0")

And to filter the funds I want I put a slicer on my page and check "621" & "821" which is from the table Funds and column fund number.

My question is how can I write a comphrenisve formula to get rid of the slicer and make room for more?

Any help is much apprecaited!

[Anonymous · Accepted solution]

Hi @Anonymous ,

You may try ALL function. It can ignore the filtering of the slicer.

For example,

Put the ID column in the slicer, then select B and C, my measure is to calculate the value of Full Code equal to 10400-0.

Measure = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Full Code]="10400-0"))

If you are still confused, please provide dummy data and expected results.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , if you putting a slicer on table UtilityCheck or joined table, then that should work 

also you can simplify it

CALCULATE( SUM ('UtilityCheck',[CY-AMT]),filter('Accounts','Accounts'[Full Code] in{ "10400-0" ,"10520-0"))

- CALCULATE( SUM ('UtilityCheck',[CY-AMT]),filter('Accounts','Accounts'[Full Code] in{ "10521-0" ,"10570-0", "10571-0"))