Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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
3 REPLIES 3
v-yingjl
Community Support
Community Support

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
Not applicable

 @v-yingjl  Hi, thanks for your reply.
 
Let me describe the situation more precisely.
This is a sample of the source dataset.
 
Dataset example.JPG
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.
 
Capture1.JPG
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
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.