Forum Discussion

abhiigt's avatar
abhiigt
Frequent Visitor
5 years ago
Solved

ALLEXCEPT DAX function not working

Hi,

 

I am using ALLEXCPET DAX function in the below sample formula :

 

Measure_Sample= calculate(count(city),ALLEXCEPT(citytable,updateddate))
 
and when I use updateddate in filter pane as a filter the value of Measure_Sample is getting effected by the filter values, ideally it should not, please correct if wrong !!! 
Need Help
  • Icey's avatar
    Icey
    5 years ago

    Hi abhiigt ,

     

    Here is one post which is similar with yours. Please check if it could help you.

     

     

    This is not really an issue. It is clearly explained in this article from Alberto. It is happening because of Auto Exist feature in DAX.

    Besides, creating a seperate dimension table is not really a workaround, but rather a best practice. A Star Schema is always considered best when working with Power BI Datamodel. This is what makes it more powerful!

     

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it faster.

3 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    abhiigt 

    ALLEXCEPT removes all context filters in the table except filters that have been applied to the specified columns.
    In your case  updateddate  columns is not affected here.

    if you need to remvoe flters from that column then use:

     

    Measure_Sample= calculate(count(city),ALL(citytable[updateddate]))

    ________________________

    If my answer was helpful, please click Accept it as the solution to help other members find it useful

    Click on the Thumbs-Up icon if you like this reply 🙂


    Website YouTube  LinkedIn



     

    • abhiigt's avatar
      abhiigt
      Frequent Visitor
      5 years ago

      Hi Fowmy,

       

      I want to block all column filters from table city, except updateddate column. Using All is also not working 😞

  • Icey's avatar
    Icey
    Community Support
    5 years ago

    Hi abhiigt ,

     

    Here is one post which is similar with yours. Please check if it could help you.

     

     

    This is not really an issue. It is clearly explained in this article from Alberto. It is happening because of Auto Exist feature in DAX.

    Besides, creating a seperate dimension table is not really a workaround, but rather a best practice. A Star Schema is always considered best when working with Power BI Datamodel. This is what makes it more powerful!

     

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it faster.