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
DataDiva
Helper II
Helper II

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

7 REPLIES 7
gjgarmatter
New Member

Did you manage to solve this? Im having a similar issue

No, I don't think I did, sorry!

TeigeGao
Solution Sage
Solution Sage

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

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. 

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.

Anonymous
Not applicable

Have you looked at using ALLEXCEPT?  

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

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

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.