cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DataDiva Regular Visitor
Regular Visitor

Keep row/column context, ignore slicers/crossfilters?

I feel like I must be missing something simple. I'm looking for a function that keeps the row/column context but ignores any slicers or crossfilters applied. Basically I want the opposite of ALLSELECT, which ignores all the row/column context but obeys any slicers or crossfiltering applied. The reason for this is that I want to create a dynamic calculation that calculates the total for that column/row based on any/all selections divided by the total for that column/row. An example would be calculating the rate of visits for each day of the week and displaying it in a bar graph like this: 

rate by day.PNGa

If no crossfilters/slicers were applied, this graph would show everything at 100%. If a slicer was applied, the slicer would remove the appropriate rows from the numerator but not the denominator. The problem if I use ALL to calcualte the denominator is that it ignores the day context as well as the slicers. I need a table that, when a slicer is applied, looks like this:

 

Day            Numerator     Denominator

Sunday       10                  50

Monday      20                  70

Tuesday      30                  40

 

ALL gives me this: 

Day            Numerator     Denominator

Sunday       10                  130

Monday      20                  130

Tuesday      30                  130

 

ALLSELECTED gives me: 

 

Day            Numerator     Denominator

Sunday       10                  60

Monday      20                  60

Tuesday      30                  60

5 REPLIES 5
Super User
Super User

Re: Keep row/column context, ignore slicers/crossfilters?

Have you looked at using ALLEXCEPT?  

https://docs.microsoft.com/en-us/dax/allexcept-function-dax

Community Support Team
Community Support Team

Re: Keep row/column context, ignore slicers/crossfilters?

Hi @DataDiva ,

Beside using the allexcept() function, we can also edit the visuals interact letting other slicer not affect current visual: https://docs.microsoft.com/en-us/power-bi/service-reports-visual-interactions

Best Regards,

Teige

DataDiva Regular Visitor
Regular Visitor

Re: Keep row/column context, ignore slicers/crossfilters?

The problem with allexcept is that it also overrides row/column context, so that doesn't accomplish what I am trying to do. 

DataDiva Regular Visitor
Regular Visitor

Re: Keep row/column context, ignore slicers/crossfilters?

Setting the vidualization to not be affected by slicers at all doesn't work, because I need the numerator in my example to be effected by the slicers, but not the denominator. 

puet93 New Member
New Member

Re: Keep row/column context, ignore slicers/crossfilters?

Did you find any solution for this?

 

I need to ignore the slicers but keep the rowcontext, im not finding how ALLEXCEPT can help me here.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,695)