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
mostvp123
Advocate V
Advocate V

Help with ALLEXCEPT ignoring my slicer selection

Sorry about the long post, I need some help with a workaround I figured out for one of my reports but some background information is needed for clarity. Unfortunately I cannot include pictures or specific data as it is sensitive information. The data I have received (Ill call it Data 1) involves various performance indicators (ie. Revenue, Gross profit) which are to be displayed in a matrix which I will refer to as Table 1. The information is to be sliced with 2 slicers, one for Date (the Date column in Data 1 is connected to a Date table) and one for "Practice", which is also connected to a separate Practice table. The company is split up into separate Practices as each sells a different product, lets call them Practice A, Practice B and Practice C. However, I cannot sum up the Practices to get the "Overall Company" as the company total performance indicators are adjusted internally due to targets needing to be met, hence
Practice A + Practice B + Practice C is not equal to Overall Company. 
The data I have recieved hence has 'Overall Company' as a separate practice, and therefore I do not compute totals for Table 1 and instead use the Practice slicer which contains: Pratice A, Practice B, Practice C and Overall Company. A single selection is required by the end user, otherwise the sum of Data 1 for the practices PLUS the Overall Company would be displayed.
Table 1 works well, however I have separate data (Ill call it Data 2) that I need to display in a separate table (Ill call it Table 2) but on the same report page. This separate data has to do with spending, and shows spend separated by "Spenders", but also needs to be sliced by Date and Practice with the SAME Practice slicer (one of the requirements of this report is to have only ONE Practice slicer on the top of the page) This separate data however does not have a separate 'Overall Company' Practice, and needs to be summed up to get to the company total. The Practice and Date columns in Data 2 are also connected to Date Table / Separate practice table (which feed into the slicers). Obviously, clicking on 'Overall Company' in the slicer results in an empty Table 2 as there is no practice in Data 2 called 'Overall Company', only Practice A, B and C. Hence I thought of the following workaround to calculate the spend in Table 2 which will find the total spend for Practice A + B + C when 'Overall Company' is selected in the slicer using SELECTEDVALUE:

Spend = 
IF( SELECTEDVALUE(Table1[Practice])="Overall Company",
      CALCULATE( SUM(Table2[Spend]),
                ALLEXCEPT('Table2','Table2'[Spender],'Table2'[Date]) 
                  ) ,
   SUM('Table2'[Spend])
)
Basically, when "Overall Company" is selected, the filter context is ignored, and all of the Spend data is displayed in the table for every Practice, except I still want to see the spend split by Spenders and I still want the Spend to change when I select a different Date in my Date slicer. The resulting table (once "Overall Company" is selected in the Practice slicer) indeed shows a summary of all practices, and the spend is split by spender, however - the Date slicer is ignored, and a sum of the spend for all dates is shown instead. If I include Date as a column in Table 2, I can see the split of spend by date but I want it to use the Date slicer, as there should be no Date column in the table. Adding 'Values('Table2'[Date]) to my CALCULATE expression just returns an empty Table 2, as I assume this brings back the filter context of 'Overall Company' not being a Practice in Data 2.
Any ideas on how I can fix this? Thank you very much for reading this far!



 

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

Hi @mostvp123 ,

 

To create a calculated table based on Table1[Practice]. Then add the Practice column of Slicer table in the slicer.

 

Slicer = VALUES(Table1[Practice])

Then update your measure. Please notice here we should not create any relationship between fact tables and slicer table.

 

Spend = 
IF( SELECTEDVALUE(Slicer[Practice])="Overall Company",
      CALCULATE( SUM(Table2[Spend]),
                ALLEXCEPT('Table2','Table2'[Spender],'Table2'[Date]) 
                  ) ,
   SUM('Table2'[Spend])
)

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

1 REPLY 1
v-frfei-msft
Community Support
Community Support

Hi @mostvp123 ,

 

To create a calculated table based on Table1[Practice]. Then add the Practice column of Slicer table in the slicer.

 

Slicer = VALUES(Table1[Practice])

Then update your measure. Please notice here we should not create any relationship between fact tables and slicer table.

 

Spend = 
IF( SELECTEDVALUE(Slicer[Practice])="Overall Company",
      CALCULATE( SUM(Table2[Spend]),
                ALLEXCEPT('Table2','Table2'[Spender],'Table2'[Date]) 
                  ) ,
   SUM('Table2'[Spend])
)

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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.