Forum Discussion

Praj8050's avatar
Praj8050
Helper I
1 year ago
Solved

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

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 :

 

 

Need Help
  • v-karpurapud's avatar
    v-karpurapud
    1 year ago

    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.

18 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    1 year ago

    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

  • v-karpurapud's avatar
    v-karpurapud
    Community Support
    1 year ago

    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's avatar
      Praj8050
      Helper I
      1 year ago

      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's avatar
        v-karpurapud
        Community Support
        1 year ago

        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