Forum Discussion
: Issue with ALLEXCEPT Function in Power BI – Unexpected Filtering Behavior
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.
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.
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
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.
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.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.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?
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.This issue is causing lot of formulas to breakdown. Is there a way to fix this or any other alternative solution
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