Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Will this work to avoid Auto-Exist . . ?

Hi everyone. A while ago, I ran into an issue related to a concept in Power BI called "Auto-Exist". Due to this, one is forced (not sure if forced is the right word) to strictly adhere to a Star Schema in Data Model. For example, have a look at the screenshot of a page from one of the reports I developed:

 

 

Here, you'll notice that there are 5 slicers affecting this page - 1. Region, 2. Rep Office, 3. Program Type, 4. Amount slider used to filter Sales Orders and 5. Adjusted Leadtime. Right now, all of these are using columns from dimesnion tables. We had to do that because in oter case, the numbers in the matrix and line chart above were incorrect (due to Auto-Exist kicking in).

Some of these slicers columns (Program Type, Adj. Leadtime) ARE part of the fact table but I had to create a separate dimesion queries for them in PBI data model by writing "select distinct . .  " queries against the DB table(s).

 

So, my question is will it work correctly, if, I create Dim tables from the single Fact table using DAX code like DISTINCT('Fact Table'[Dim Column]) and then create a relationship between that calculated dim table and the fact table ? Or am I missing something that happens in the background in DAX which will render this solution futile ? has anyone tried out something like that before ?

 

Any help/input is appreciated. Thanks in advance.

 

- Nachiket

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Not very clear. We should prefer star schmea, but it will work without that too. Not sure what do you mean by Auto-Exist.

    An example where the star schema plays a role. Say I want to ignore filter on date, but do not want other filters to get impacted , then we can use all on date table

     

    It also help to force row context after using all or allselected

    This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))

     

    In the above case, you do need to worry about other dimensions, you will get the current period for all dimensions correctly.

     

    If I use it in a single table I have to add condition to handle all other parameters

     

     

    Can explain with example what is the issue

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    In a regular star schema where dimensions are linked to the fact table, auto-exist only operates on dimensions.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.

    For more details,you can refer to:https://www.sqlbi.com/articles/understanding-dax-auto-exist/ 


    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.