cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

DAX Measure ignore filter/slicer selection

Hi,

 

I'm interested in creating a measure that doesn't change with Filter/Slicer settings to be included as a benchmark.

I've tried writing:

 

Revenue = CALCULATE(SUM(Table[Amount]);Table[Category]="Revenue";ALL(Table[City]))

 

However this still effects the measure when clicking on the filter

 

Br

Marcus

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: DAX Measure ignore filter/slicer selection

Hi @Marcuskw,

 


Is there a way to have the second table show all citys even if you clicked "Hamburg"? 

No. Since the slicer is related to table visual, it is not possible to still show all rows when you have selected an item in slicer. Unless you disable the interaction between slicer and visual, as mentioned by TomMartens.

 

To workaround your requirement, you could create an unrelated table which lists all available city names. That case, when you choose any value from slicer, the table visual won't be filtered by it. You said some other measures should still be affected by slicer selection, you could add filters into these measures. For example:

 

Selected city =
SELECTEDVALUE ( Table[Category] )



Measure =
CALCULATE (
    SUM ( Table[value] ),
    FILTER ( Table, Table[City] = [selected city] )
)

 

Regards,

Yuliana Gu

 
Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Highlighted
Super User IV
Super User IV

Re: DAX Measure ignore filter/slicer selection

Is your filter based on City? 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


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User III
Super User III

Re: DAX Measure ignore filter/slicer selection

Hey,

 

as you can see from this little screenshot

image.png

 

that the measure "All Revenue" is not affected by a slicer selection, here is the DAX for the measure:

All Revenue = 
CALCULATE(
    SUM(Table1[Amount])
    ,'Table1'[Category] = "Revenue"
    ,ALL('Table1'[City])
)

I'm wondering if there are more slicer / filter involved in your table that may affect the calculation.

 

Regards

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Highlighted
New Member

Re: DAX Measure ignore filter/slicer selection

Hi TomMartens,

My aim is to have multiple measures in the same table where one interacts with the filter value and one doesn't.

I see that you edited the second table to not interact with the filter selection(Edit interactions and "None")

 

Is there a way to have the second table show all citys even if you clicked "Hamburg"?

 

Thanks

Marcus

Highlighted
Microsoft
Microsoft

Re: DAX Measure ignore filter/slicer selection

Hi @Marcuskw,

 


Is there a way to have the second table show all citys even if you clicked "Hamburg"? 

No. Since the slicer is related to table visual, it is not possible to still show all rows when you have selected an item in slicer. Unless you disable the interaction between slicer and visual, as mentioned by TomMartens.

 

To workaround your requirement, you could create an unrelated table which lists all available city names. That case, when you choose any value from slicer, the table visual won't be filtered by it. You said some other measures should still be affected by slicer selection, you could add filters into these measures. For example:

 

Selected city =
SELECTEDVALUE ( Table[Category] )



Measure =
CALCULATE (
    SUM ( Table[value] ),
    FILTER ( Table, Table[City] = [selected city] )
)

 

Regards,

Yuliana Gu

 
Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Frequent Visitor

Re: DAX Measure ignore filter/slicer selection

HI there,

 

Can you let me know how I would write the formula where, if instead of calculating the total Revenue, I wanted my measure to always be the total of only 1 city.

 

For example, If you have Hamburg selected as shown, but I want Toronto Revenue to be my "All Revenue" measure....my table on the right would show "Hamburg, Revenue, 100, 75"

 

Is this possible?

Highlighted
Anonymous
Not applicable

Re: DAX Measure ignore filter/slicer selection

i am having meaures column in %

ID Value

A  10

b   20                                      so slicer should show two categories A 

c    30                                                                                                 B

d    40

e    50

f     60

 

if(value >30 ,value<50,"A","B")

But i am getting only one value in slicer ,how to achieve two values in slier

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors
Top Kudoed Authors