Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Best Way to Implement a Filter for non Calculations:

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!
DAX
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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"))

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.