Measure ignoring slicer selection but adding new filters at the same time

[Anonymous]

Hi,

I am working on the following measure which however doesn't provide the expected result. The purpose of this measure is to sum column 'All base period' ignoring the clicer selection applied to column 'Segment' but adding two additional filters pertinent to columns 'Geography' and 'Cost Center' 

RegionOwnedInternational =  CALCULATE(SUM('AO source'[All base period]), ALL('AO source'[Segment]),Filter('AO source','AO source'[Geography]= "International" &&'AO source'[Cost Center]= "Region Owned"))

The result of this below DAX formula is 'blank'. It works correctly if I tamper with slicer interactions but eventually it's not an option because there's a couple of measures in this report which are mutually dependent so I need to have the values computed rather than steered through interactions.

The measure computes some result (not what I want though) if I delete this part: 

Filter('AO source','AO source'[Geography]= "International" &&'AO source'[Cost Center]= "Region Owned"

hence I guess there must be some conflict between ALL and Filter components. 

Would you have any idea what I need to change in the formula ? 

Regards

Krzysztof

[v-yingjl]

Hi @Anonymous ,

I'm not aware of your dataset so I'm not certain what you want to calculate and which reason cause this issue based on your current description. Maybe modify like this?

RegionOwnedInternational =
CALCULATE (
    SUM ( 'AO source'[All base period] ),
    FILTER (
        ALL ( 'AO source'[Segment] ),
        'AO source'[Geography] = "International"
            && 'AO source'[Cost Center] = "Region Owned"
    )
)

If you want to know about All() and Filter(), you can refer:

1. All() 
2. Filter() 

Could you please consider providing some sample data for further discussion? Sample data and expected output would help tremendously.

Best Regards,
Yingjie Li

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

[Anonymous]

 @v-yingjl  Hi, thanks for your reply.

Let me describe the situation more precisely.

This is a sample of the source dataset.

Measure which I was referring to in my question called RegionOwnedInternational is supposed to summarize all the values in column 'AO source'[Management base period] for segments except for the ones selected in the slicer (Segment) but including filters on Cost Center = Region Owner.

I modified the DAX code so that it now captures all the values in tab  'AO source'[Management base period] on segments different to the one/s selection via the slicer but the question is how to bake in this component therein 

'AO source'[Cost Center] = "Region Owned"

 I tried adding Filter then nesting ALL function into it but it always displays some syntax errors.

RegionOwnedInternational =

VAR __Segment = VALUES ( 'AO source'[Segment])

VAR __selectedSegment = SELECTEDVALUE ( 'AO source'[Segment])

RETURN

CALCULATE(

SUM ( 'AO source'[Management base period] ),

ALL ( 'AO source'[Segment]) ,

IF ( __selectedSegment == BLANK(), 1 , NOT 'AO source'[Segment] IN __Segment )

)

Regards

Krzysztof

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490