: Issue with ALLEXCEPT Function in Power BI – Unexpected Filtering Behavior

[Praj8050]

Dear Power BI Community,

I am facing an issue with the ALLEXCEPT function in Power BI, and I am reaching out to seek guidance and a potential solution.

Details of the Issue

I have used the following formula to calculate Total Smartphone Units:

Total Smartphone Units = CALCULATE(Smartphone[Smartphone Units], ALLEXCEPT(Smartphone, Smartphone[Place], Smartphone[Week], Smartphone[Smartphone Brand]))

Expected Behavior:

When I filter the Accessories Category, the calculated measure Total Smartphone Units should remain unchanged since the Accessories Category field is not included in the ALLEXCEPT function.

Observed Behavior:

Contrary to expectations, when the Accessories Category is filtered, the Total Smartphone Units value is also being filtered.

Specific Observation:

This issue occurs only when the Week filter is applied. Without the Week filter, the measure behaves as expected, and the Accessories Category filter does not impact the Total Smartphone Units calculation.

Before filtering Accessories Category

After filtering Accessories Category

Request for Assistance:

Could you please help me understand why this behavior is occurring? Is there a limitation with the ALLEXCEPT function in this context, or am I missing something in my formula setup

Screenshot from Data file :

[v-karpurapud]

Hi @Praj8050 

Thanks for reaching out to the Microsoft Fabric Community Forum.

While this formula which was used before is intended to ignore filters outside of the specified columns, it does not explicitly exclude the Accessories Category filter.

1.To fix the issue, explicitly remove the Accessories Category filter using REMOVEFILTERS or ALL.

Here is the modified DAX  measure

Total Smartphone Units =

CALCULATE(

 SUM(SmartPhones[Smartphone Units]),

 REMOVEFILTERS(Accessories[Accessories Category]),

 ALLEXCEPT(SmartPhones, SmartPhones[Place], SmartPhones[Week],      SmartPhones[Smartphone Brand])

)

2.Now  Test the Measure

1. Add a Matrix Visual or Table Visual in Power BI.
2. Add the following fields(Rows: Smartphone[Smartphone Brand],Columns: Smartphone[Week],Values: Total Smartphone Units).
3. Apply the following filters and observe the behavior:
• Accessories[Accessories Category]: Select "Case" or "Charger".
• Expected Result: The Total Smartphone Units measure remains unchanged.
• Smartphone[Week]: Apply a filter for specific weeks.
• Expected Result: The measure reflects only the weeks selected but remains unaffected by the Accessories Category filter.

Have a look at the images below:

I hope this approach will get the solution as per the requirements which was mentioned above. If you’re still experiencing issues, feel free to reach out to us for further assistance!

If you find this post helpful, please mark it as an "Accept as Solution" and give a KUDOS.

[Praj8050]

Hi  v-karpurapud,

I tried the new formula you shared, but I am still getting incorrect results. The formula works correctly without applying the Week filter; however, as soon as I apply the Weekly filter along with the Accessories filter, it breaks. Specifically, changes made in the Accessories category are affecting the Total Smartphone Units.

Please find the Power BI file link

https://drive.google.com/file/d/1VBLNLqp2oa6mWgbb_9_5O4bLIqixhwuG/view?usp=drive_link

Note : The dax formula you have shared is updated in the field name : New Total Smartphone Units

[v-karpurapud]

Hi @Praj8050 
I have raised a request to access the file which you have shared. Could you please grant me access. So that i can deep dive more into the issue and i will give the solution as per the requirements.
Thank You

[Praj8050]

Hi v-karpurapud

Acces has been granted

[v-karpurapud]

Hi @Praj8050 
Hope you are doing well!

Based on the report these are following observations

Observation-1: When a single date is selected in the Week Field, applying a filter on the Accessories Category does not affect the Total Smartphone Units Field.
Observation-2: When multiple dates are selected in the Week Field, applying a filter on the Accessories Category does affect the Smartphone Units Field.

Here is the detailed explanation of the above observations:

Before Applying filters

Ex: Scenario-1 When I select a date 2022-10-01 in the week field , in accessories category field contains only two items such as phone case and screen guard. If we select any of these items or multiple items, the total smartphone units field remains unchanged. Have a glance at the picture below for better understanding.

After applying the filters(Single selection in week filed & Accessories Category)

After applying the filters(Single selection in week filed & multiple selections in Accessories Category)

Scenario-2 When I select multiple dates 2022-10-01,2022-10-02,2022-10-03 in week field , in accessories category field contains more items such as charging cable, memory card, earphone, mobile charger, screen guard, phone case, sim ejector. If we select anyone or multiple items, the total smartphones units field is changing.

This variance is because of filter limitations as we are selecting multiple dates in week field which leads to the addition of more number of items in accessories category based on those particular dates. In this case if a filter is applied on accessories categories it will automatically affect the total smartphone units. Because we are selecting multiple dates which includes each day total count to the entire total smartphone units value. Due to this reason the total smartphones units field is changing after applying the accessories category when we select multiple dates in the week field.

If you find this post helpful, please mark it as an "Accept as Solution" and give a KUDOS.

[Praj8050]

Hi @v-karpurapud 

Why does selecting multiple dates in the week filter cause the ALLEXCEPT function to break? By definition, the ALLEXCEPT function removes all filters except those explicitly applied to the columns specified in its arguments.

Does this behavior indicate a limitation of the ALLEXCEPT function? If so, is there a way to resolve this issue, or is there an alternative method to achieve the desired outcome?

[v-karpurapud]

Hi @Praj8050 

No, this is not a limitation of the ALLEXCEPT function itself. The issue arises from how filters and contexts are handled when you select multiple dates. The function is working as designed as it’s keeping the specified columns (Place, Week, Smartphone Brand) in the context, but the aggregation over multiple dates is influencing the result, making the Accessories filter impact the total.

Alternative approach:

Try to enable edit interactions for particular field

In the scenario When I filter the Accessories Category, the calculated measure Total Smartphone Units should remain unchanged since the Accessories Category field is not included in the ALLEXCEPT function.

For this case if we apply edit interactions to table visual with respect to accessories category even after applying the filter also the total smartphones unit field won't affect.

Enable edit interactions:

1.On the ribbon at the top, click on the Format tab. Then click on Edit interactions. This will show interaction options on the report.

2. Choose Interaction Types:
Upon activating Edit interactions, a small icons appear above other visuals.

Filter (funnel icon): Clicking this will filter the target visual based on the selection in the source visual.
None (circle with a line through it): Clicking this will prevent the source visual from affecting the target visual.

3. Set the Desired Interactions:
Click on the filter or none icons for each visual to set the interaction behavior according to your needs.

4.Exit Edit Mode:
Once configured the interactions as desired, click the Edit interactions button again to exit the edit mode.

I have enabled edit interactions in page1 in the report which was shared by you.
I hope this approach resolved the issue caused by accessories category filter , allowing for filtering the week table correctly and calculating the total smartphone units between the multiple dates.

If you find this post helpful, please mark it as an "Accept as Solution" and give a KUDOS.

[Praj8050]

Hi @v-karpurapud,

I have reviewed the workbook you shared and noticed an issue after enabling the editing interaction:

Changes made in the Accessories category are not impacting the Total Smartphone Units, as expected. However, the Accessories Units are also not updating based on the selections made in the Accessories filter, which is incorrect.

The Accessories filter should affect only the Accessories Units and should not impact the Smartphone Units 

[v-karpurapud]

Hi @Praj8050 

The expected solution is not possible because the issue arises from filters and contexts are handled when multiple dates are selected.When you select multiple dates, the filters and contexts can cause issues. Specifically, it sums up the total values for each date, which changes the total number of smartphone units. To address this, the recommended approach is to create separate visuals based on the filtering categories and configure the interaction settings for these visuals. This ensures that the desired output is achieved without the totals being inaccurately combined.

If you have any further questions or need additional help with this, feel free to reach out     to us for further assistance!

If you find this post helpful, please mark it as an "Accept as Solution" and give a KUDOS.

[v-karpurapud]

Hi @Praj8050 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-karpurapud]

Hi @Praj8050 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[Praj8050]

Hi @v-karpurapud,

I need to incorporate the Accessories category and Total Smartphone Units into a single formula:

Accessories Attached Rate% = Accessories Units / Total Smartphone Units

Since they cannot be used separately in different visuals, I am looking for a solution to achieve this.Could you please advise if there is a way to implement this?

[v-karpurapud · Accepted solution]

Hi @Praj8050 

Unfortunately, there's no other way to implement this. The issue comes from how filters and contexts are managed when multiple dates are selected. The total number of smartphone units changes based on the filter selection. When you select multiple dates, the total smartphone units change because different accessories are added for each specific date. This is why the total changes.

If you find this post helpful, please mark it as an "Accept as Solution" and give a KUDOS.

[Praj8050]

This issue is causing lot of formulas to breakdown. Is there a way to fix this or any other alternative solution

[Praj8050]

I don't understand why the ALLEXCEPT function breaks only when multiple filters are applied. According to the documentation, it should handle this scenario. Is this a bug or a limitation of the ALLEXCEPT function

[TomMartens]

Hey @Praj8050 

check your data distribution, maybe the week is influencing the category, or rephrasing it a little: maybe categories and weeks are dependent variables.

Besides that, w/o being able to look at sample data I can not provide any further guidance, except pointing you to the ALLEXCEPT article here: https://dax.guide/allexcept/

If checking the data distribution and the article are not enough to tackle your challenge, consider preparing a pbix containing sample data that still represents your semantic model (tables, relationships, calculated columns, and measures). Upload the pbix to OneDrive, Google Drive, or Dropbox and share the link. Do not forget to explain the expected behavior based on the provided data.

Regards,

Tom

[Praj8050]

Hi TomMartens,

Please find the Power BI & Data file links

Power BI :

https://drive.google.com/file/d/1VBLNLqp2oa6mWgbb_9_5O4bLIqixhwuG/view?usp=drive_link

Datafile : https://docs.google.com/spreadsheets/d/1GTO7DD5Ae02OJo1NyiYePM8IsS90zp8Zt1DysjEdaXY/edit?usp=drive_l...

[Praj8050]

Data excel file link
: https://docs.google.com/spreadsheets/d/1GTO7DD5Ae02OJo1NyiYePM8IsS90zp8Zt1DysjEdaXY/edit?gid=1725197...

Total Smartphone Units = CALCULATE(Smartphone[Smartphone Units],ALLEXCEPT(Smartphone,Smartphone[Place],Smartphone[Week],Smartphone[Smartphone Brand]))

Excepted Output

Output after applying Accessories filter

The Accessories Category field is not included in the ALLEXCEPT function; however, it is still filtering the Total Smartphone Units when applied.