Forum Discussion

bswylie's avatar
bswylie
Helper I
9 years ago
Solved

ALL() function ignored / inconsistent functionality when also using report level filter

While working on an analysis report we came across (what seems to be) an issue in Power BI, and are not sure if this is a bug or a misunderstanding on our part. In summary, a measure we have created ...
Need Help
  • bswylie's avatar
    bswylie
    9 years ago

    Hi,

     

    I got a response from Microsoft to my ticket and wanted to share the result. This did fix the problem as per screenshot below. I'm not sure why there is a difference between PBI and PowerPivot results, but I'm grateful to have something to work with. It is not clear whether the Power BI support team considers this a bug or not as they refer more to "problem" and "workaround". I was under the impression everything could be done from a flat table in PBI if only one source being used, but it's not much admin to add to link up at least. Thanks all for the input.

     

    Microsoft support response

    Use the ALL function on the dimension attribute instead of the column in the fact table, and use the slicer on the dimension attribute, this seems to workaround the problem.

     

     

ttrandai's avatar
ttrandai
Frequent Visitor
9 years ago

Did Microsoft ever solve the bug? I am currently experiencing a similar problem, however I do not understand the solution provided in this post.

efigoldman's avatar
efigoldman
Frequent Visitor
9 years ago

You need to use a star Schema.

So that the attribute you use in the All () function will function well.

 

For example - if you wish to see ALL Product sales, no matter what you choose in the slicer, you need to use a product table connected to the fact (sales) table. 

Then use the All() function on a column in the  Product table. this should work well. 

  • johndkn's avatar
    johndkn
    Frequent Visitor
    9 years ago

    I found out that the ALL() function is ignored when it is applied to a column that is "sorted by" another column ... When I remove the "Sort By Column", it's working again... 

    • Sean's avatar
      Sean
      Community Champion
      7 years ago

      Hello bswylie !

       

      The SQLBI guys just wrote an article about this.

      If you'd like add this link to your post you marked as solution.

      Since the solution shows up on the first page immediately after the question other people can see it right away.

      https://www.sqlbi.com/articles/understanding-dax-auto-exist/

       

      ā€œThe golden rule of data modeling is always the same: always use star schemas.

      If a column has to be used to slice and dice, then it needs to belong to a dimension.

      Numbers to aggregate, on the other hand, are stored in fact tables.

      Tabular lets a developer deviate from the regular star schema architecture.

      This does not mean that doing it is always a good idea. It seldom is.ā€

      Alberto Ferrari

       

      The SQLBI guys wrote the above article in response to a recent question similar to yours:

      https://community.powerbi.com/t5/Desktop/Suspected-BUG-when-using-ALL-with-multiple-non-ignored-slicer/m-p/506200#M236361

       

      Thanks! :smileyhappy: