Forum Discussion
Community ChampionWeird behavior after creating relationship
I think this article explains what you are seeing.
Here is the relevant snippet
In order to simplify the usage of time intelligence functions, the DAX engine makes an assumption when two tables are related through a column of Date data type: When a filter is applied on the key of the relationship – Date[Date] in this example – the new filter overrides any other filter on the Date table. It basically applies a REMOVEFILTERS ( Date ) to the filter context every time you apply a filter on the Date[Date] column. This behavior occurs automatically only when the relationship is based on a column of Date data type.
You can obtain the same behavior – that is, adding REMOVEFILTERS on the table whenever a new filter is applied on the Date column – by marking the table as a date table. When you mark a table as a date table, Power BI asks which column contains the dates of the calendar. This is required because the engine adds REMOVEFILTERS every time you apply a filter on that specific column.
Regards,
Pat
Hey,
late to the party, but nevertheless I want to share the link to one of my favorite DAX articles. This one has been written by Jeffrey Wang some time ago:
MDX and DAX topics: DAX Time Intelligence Functions (mdxdax.blogspot.com)
As mahoneypat already mentioned the behavior is related to the internal workings of related date columns. Here is the relevant snippet from this article:
If a Calculate filter has a unique column that is of data type date/time, all previous filters on all columns from the table which contains this date/time column are removed.I still read this article, whenever I'm facing some more complex DAX challenges where the calendar plays its part, sometimes in preparation, but most of the time to fix things 🙂
Regards,
Tom
Community ChampionMany thanks for your reply. These pieces of text seem to reinforce my opinion. On the first 1, the relevant part is
If the columns (or tables) aren't in the filter context, then new filters will be added to the filter context to evaluate the expression.
That simply supports what I am saying
The second one also states that the filter expression overrides the corresponding filters over the same column(s).
Again, it corroborates my view.
I am attaching another example that is very similar to the original one in structure but shows exactly the behaviour I expect, the standard one in my view. Both with and without relationships.
In any case, the question still remains as to why creating the relationship in the first file causes the change in behaviour in the measure.
I'll bring in some other people to hear their thoughts
TomMartens MFelix mahoneypat lbendlin edhans
Thank you
Super UserHey,
late to the party, but nevertheless I want to share the link to one of my favorite DAX articles. This one has been written by Jeffrey Wang some time ago:
MDX and DAX topics: DAX Time Intelligence Functions (mdxdax.blogspot.com)
As mahoneypat already mentioned the behavior is related to the internal workings of related date columns. Here is the relevant snippet from this article:
If a Calculate filter has a unique column that is of data type date/time, all previous filters on all columns from the table which contains this date/time column are removed.I still read this article, whenever I'm facing some more complex DAX challenges where the calendar plays its part, sometimes in preparation, but most of the time to fix things 🙂
Regards,
Tom
- AlB
- TomMartens
My pleasure!